<?php
/**
 * User: xuanbang.wen
 * Date: 2019/8/18
 */

namespace App\Web\Model;

use App\Customer\Model\ResumeRecommend;
use App\Service\DbIndex;
use App\Web\Facades\UserAuth;
use App\Web\Model\CaseResume as CaseResumeModel;
use App\Web\Model\Project as ProjectModel;
use App\Web\Model\RabbitMQ\ResumeUpdate;
use App\Web\Model\User as UserModel;
use EchoPHP\Facades\Db;
use EchoPHP\Facades\Log;
use EchoPHP\Facades\Redis;
use EchoPHP\Facades\Validation;
use Exception;
use Ramsey\Uuid\Uuid;
use RuntimeException;
use Throwable;

class CaseModel extends Model {
    private $tablename = 'SCase';
    protected $field = 'caseid,casename,subid,projectid,salaryfrom,salaryto,area,creator,createdate,duedate,status';
    private $batchFields = ['status', 'listindex'];
    private $recentCaseMax = 20;        //redis中最近职位最大数量
    private $caseCountMax = 100;        //项目下最大职位数

    /**
     * 获取用于参与职位数以及项目下职位总执行顾问人数
     * @param $userId
     * @param $projectId
     * @return array
     */
    public function getProjectCaseAndOprCount($userId, $projectId) {
        $sql = 'select count(DISTINCT a.oprid) as oprcount, count(DISTINCT a.caseid) as casecount from CaseOperator a INNER JOIN CaseOperator b on a.caseid=b.caseid where b.oprid=? and b.projectid=? and b.status=?';
        $var = [$userId, $projectId, '01'];
        return Db::selectOne($sql, $var);
    }

    /**
     * Notes:公司列表（客户/项目对接人）
     * @param $userid
     * @return array
     */
    public function subOprSub($userid) {
        //公司对接人
        $sql = 'select c.subid,c.subname from CustomerSub as c
                left join SubOperator as so on so.subid = c.subid
                where so.oprid = ? and so.status = ? ';
        $sub = Db::select($sql, [$userid, '01']);
        //项目对接人
        $sql = 'select c.subid,c.subname from CustomerSub as c
                left join ProjectOperator as po on po.subid = c.subid
                where po.oprid = ? and po.status = ? ';
        $projSub = Db::select($sql, [$userid, '01']);
        $result = array_unique(array_merge($sub, $projSub), SORT_REGULAR);
        if (!$result) {
            return [
                'unfinished' => [],
                'finished' => [],
            ];
        }
        $sub = [];
        foreach ($result as $value) {
            $sub[$value['subid']] = $value['subname'];
        }

        $subids = array_column($result, 'subid');
        $inFlag = substr(str_repeat('? ,', (count($subids))), 0, -1);
        $sql = 'select subid,status from ' . $this->tablename . ' where subid in  ( ' . $inFlag . ' )';
        $subArr = Db::select($sql, $subids);
        $finished = $unfinished = [];
        foreach ($subArr as $item) {
            //未完成职位
            if ($item['status'] != '03') {
                if (isset($sub[$item['subid']])) {
                    $tmp['subid'] = $item['subid'];
                    $tmp['subname'] = $sub[$item['subid']];
                    $unfinished[] = $tmp;
                }
            } else {
                if (isset($sub[$item['subid']])) {
                    $tmp['subid'] = $item['subid'];
                    $tmp['subname'] = $sub[$item['subid']];
                    $finished[] = $tmp;
                }
            }
        }
        return [
            'unfinished' => $unfinished,
            'finished' => $finished,
        ];
    }

    //项目列表- 公司或客户对接人
    public function subOprProj($userid, $subid, $isfinished) {
        //公司对接人
        $sql = 'select p.projectid,p.projectname from Project as p
                left join SubOperator as so on so.subid = p.subid
                where so.oprid = ? and p.subid = ? and so.status = ? ';
        $sub = Db::select($sql, [$userid, $subid, '01']);
        //项目对接人
        $sql = 'select p.projectid,p.projectname from Project as p
                left join ProjectOperator as po on po.subid = p.subid
                where po.oprid = ? and p.subid = ? and po.status = ? ';
        $projSub = Db::select($sql, [$userid, $subid, '01']);
        $result = array_unique(array_merge($sub, $projSub), SORT_REGULAR);

        if (!$result) {
            return [];
        }
        //匹配其项目下case以及状态，剔除不在此对接人下以及case status不对的职位
        $projectids = array_column($result, 'projectid');

        $inFlag = substr(str_repeat('? ,', (count($projectids))), 0, -1);
        $sql = 'select distinct c.projectid from ' . $this->tablename . ' as c
                left join CaseOperator as co on c.caseid = co.caseid
                where  c.projectid in  ( ' . $inFlag . ' ) ';
        $sql .= $isfinished ? 'and c.status = ?' : 'and c.status != ?';
        $caseProjids = Db::select($sql, array_merge($projectids, ['03']));
        if (!$caseProjids) {
            return [];
        }
        foreach ($result as $k => $item) {
            if (!in_array($item['projectid'], array_column($caseProjids, 'projectid'))) {
                unset($result[$k]);
            }
        }
        return array_values($result);
    }

    /**
     * 职位管理、人才管理 查询客户列表
     * @param $userId       当前登录人id
     * @param string $subName 查询 客户名称
     * @param string $projectName 查询项目名称
     * @return array
     */
    public function getSubList($userId, $subName = null, $projectName = null) {
        $sql = 'SELECT a.subid,a.subname,if(b.status=?, ?, ?) as projectfinished FROM CustomerSub a LEFT JOIN Project b on a.subid=b.subid LEFT JOIN CaseOperator c on b.projectid=c.projectid WHERE c.status=? AND c.oprid=?';
        $var = ['03', 1, 0, '01', $userId];
        if (!empty($subName)) {
            $sql .= ' AND a.subname LIKE ?';
            $var[] = '%' . $subName . '%';
        }
        if (!empty($projectName)) {
            $sql .= ' AND b.projectname LIKE ?';
            $var[] = '%' . $projectName . '%';
        }
        $sql .= ' GROUP BY a.subid,b.status order by a.subid desc';
        $result = Db::select($sql, $var);
        $subList = [
            'unfinished' => [],
            'finished' => [],
        ];

        $subIds = [];
        foreach ($result as $item) {
            $item['top'] = 0;
            if ($item['projectfinished'] == 1) {
                $subList['finished'][$item['subid']] = $item;
            } else {
                $subList['unfinished'][$item['subid']] = $item;
                $subIds[] = $item['subid'];
            }
        }
        unset($result);

        //获取存放在redis中的置顶元素合计以对客户列表进行排序
        $redisClient = Redis::connect('topsubids');
        $keyName = 'topsubids:unfinished:oprid:' . $userId;
        $unfinishedTopSubIds = $redisClient->zRevRange($keyName, 0, -1);
        $unfinishedTopSubIds = $unfinishedTopSubIds === false ? [] : $unfinishedTopSubIds;
        $uselessSubIds = array_diff($unfinishedTopSubIds, $subIds);//置顶公司id中不存在于实际公司列表的公司id
        $unfinishedTopSubIds = array_diff($unfinishedTopSubIds, $uselessSubIds);//实际的置顶公司id列表

        $topSubList = [];
        foreach ($unfinishedTopSubIds as $topSubId) {
            $subList['unfinished'][$topSubId]['top'] = 1;
            $topSubList[] = $subList['unfinished'][$topSubId];
            unset($subList['unfinished'][$topSubId]);
        }

        //比对结果总存在不再客户列表中的数据，表示之前置顶，但是现在已经无法查看，从redis中移出，以清除脏数据
        if (!empty($uselessSubIds)) {
            $redisClient->zRem($keyName, ...$uselessSubIds);
        }

        $subList['unfinished'] = array_merge($topSubList, array_values($subList['unfinished']));
        $subList['finished'] = array_values($subList['finished']);
        return $subList;
    }

    /**
     * 根据客户id获取客户下项目列表
     * @param $userId   当前登录人id
     * @param $subId    客户id
     * @param $isFinished   职位状态  true：已结束 false： 未结束（进行中、暂停）
     * @param null $projectName 项目名称
     * @return array
     */
    public function getProjectListBySubId($userId, $subId, $isFinished, $projectName = null) {
        $sql = 'SELECT a.projectid,a.projectname FROM Project a LEFT JOIN CaseOperator b on a.projectid = b.projectid WHERE b.status=? AND b.oprid=? AND a.subid=?';
        $var = ['01', $userId, $subId];
        if (!$isFinished) {
            $sql .= ' AND a.status<>?';
            $var[] = '03';
        } else {
            $sql .= ' AND a.status=?';
            $var[] = '03';
        }
        if (!empty($projectName)) {
            $sql .= ' AND a.projectname LIKE ?';
            $var[] = '%' . $projectName . '%';
        }
        $sql .= ' GROUP BY a.projectid ORDER BY a.projectid DESC';
        $result = Db::select($sql, $var);
        return $result;
    }

    /**
     * Notes:创建职位
     * @param $subId
     * @param $projectId
     * @param $params
     * @return array|mixed
     * @throws Throwable
     */
    public function create($subId, $projectId, $params) {
        $userId = UserAuth::getLoginId();
        //检查项目状态是否为进行中
        $result = $this->chkProjectStatus($projectId, 'projectid');
        if (empty($result)) {
            return [
                'status' => false,
                'message' => '项目非进行中，无法新增',
                'caseid' => 0
            ];
        }

        //检查项目下职位数量
        $sql = 'select count(1) as count from SCase where subid=? and projectid = ? and status = ?';
        $var = [$subId, $projectId, '01']; //, '03'
        $caseCount = Db::select($sql, $var)[0]['count'];

        $areaCount = 1;
        if (!$params['separate'] == 0) {
            $areaCount = count(explode(',', $params['area']));
        }
        if ($caseCount + $areaCount > $this->caseCountMax) {
            return [
                'status' => false,
                'message' => '项目下职位数已达上限',
                'caseid' => 0
            ];
        }

        $projectModel = new ProjectModel();
        $projectOpr = $projectModel->getProjectOpr($subId, $projectId, '1');
        $projectChargerId = $projectOpr[0]['oprid'] ?? 0;
        if ($projectChargerId < 1) {
            return [
                'status' => false,
                'message' => '该项目不存在项目负责人，无法新增职位',
                'caseid' => 0
            ];
        }
        // 整理职位执行人，职位的项目负责人及当前操作人一定在职位执行人列表中
        $oprIds = [];
        if ($params['oprid']) {
            $oprIds = explode(',', $params['oprid']);
        }
        $oprIds[] = $projectChargerId;
        $oprIds[] = $userId;
        $oprIds = array_unique($oprIds);
        try {
            return Db::connect('jyrpo_pdo')->transaction(function () use ($subId, $projectId, $params, $oprIds, $userId) {
                if ($params['separate'] == 0) {
                    //多地区加入同一职位
                    $areas = [$params['area']];
                } else {
                    //多地区区分为不同职位
                    $areas = explode(',', $params['area']);
                }
                $caseInfoParams = [
                    $params['casename'], $subId, $projectId, $params['salaryfrom'],
                    $params['salaryto'], $params['content'], $userId, $userId,
                    '01', $params['attachid'], $params['listindex'], $params['caseshowname'],
                    $params['vacancy'],
                ];

                $insertCases = [];

                //循环保存每一个职位
                foreach ($areas as $area) {
                    $caseInfoInsVar = $caseInfoParams;
                    $caseInfoInsVar[] = $area;
                    $caseInfoInsSql = 'INSERT INTO ' . $this->tablename . ' (casename,subid,projectid,salaryfrom,salaryto,content,creator,updator,status,attachid,listindex,caseshowname,vacancy,area,createdate,updatedate) value (' . mergeArrSql($caseInfoInsVar) . ',NOW(),NOW())';
                    Db::connect('jyrpo_pdo')->insert($caseInfoInsSql, $caseInfoInsVar);
                    $caseId = Db::connect('jyrpo_pdo')->getLastInsertId();

                    $caseOperatorInsSql = 'insert into CaseOperator (subid,projectid,caseid,oprid,status,isowner,issys,createdate,updatedate) values';
                    $caseOperatorInsVar = $caseOprVarsSql = [];
                    foreach ($oprIds as $oprId) {
                        $caseOprVarsSql[] = '(?,?,?,?,?,?,?,NOW(),NOW())';
                        $caseOperatorInsVar = array_merge($caseOperatorInsVar, [
                            $subId, $projectId, $caseId, $oprId, '01', 0, 0
                        ]);
                    }
                    $caseOperatorInsSql .= implode(',', $caseOprVarsSql);
                    Db::connect('jyrpo_pdo')->insert($caseOperatorInsSql, $caseOperatorInsVar);

                    $insertCases[] = ['caseid' => $caseId, 'casename' => $params['casename']];
                }

                return [
                    'status' => true,
                    'message' => '创建成功',
                    'insertcases' => $insertCases
                ];
            });
        } catch (Exception $exception) {
            Log::error('创建职位' . $params['casename'] . '失败,errormessage:' . $exception->getMessage(), ['params' => $params, 'userid' => $userId]);
            return [
                'status' => false,
                'message' => '创建失败',
                'caseid' => 0
            ];
        }
    }

    /**
     * @param $params
     * @param $allProjOpr
     * @return array
     */
    public function batchCreate($params) {
        $errorList = [];
        $sql = 'INSERT INTO ' . $this->tablename . ' (casename,caseshowname,listindex,subid,projectid,salaryfrom,salaryto,content,creator,updator,status,vacancy,area,batchcode,createdate,updatedate) values ';
        $val = $values = $uuids = [];
        foreach ($params as &$param) {
            $valTemplate = [
                $param['casename'],
                $param['caseshowname'],
                $param['listindex'],
                $param['subid'],
                $param['projectid'],
                $param['salaryfrom'],
                $param['salaryto'],
                $param['content'],
                //$param['duedate'],
                $param['creator'],
                $param['updator'],
                '01',
                $param['vacancy'],
            ];

            if ($param['separate'] == false) {
                $areaArr = [implode(',', $param['area'])];
            } else {
                $areaArr = $param['area'];
            }

            foreach ($areaArr as $item) {
                $values[] = '(?,?,?,?,?,?,?,?,?,?,?,?,?,?,NOW(),NOW())';
                $val1 = $valTemplate;
                $val1[] = $item;
                $uuidTmp = Uuid::uuid1()->toString(); //$param['uuid']
                $val1[] = $uuidTmp;

                $uuids[] = $uuidTmp;
                $param['uuids'][] = $uuidTmp;

                $val = array_merge($val, $val1);
            }
        }
        unset($param);
        $sql .= implode(',', $values);
        $result = Db::insert($sql, $val);
        if (!$result) {
            return [
                array_column($params, 'No'),
            ];
        }

        $inFlag = substr(str_repeat('? ,', (count($uuids))), 0, -1);
        $sql2 = 'select caseid,batchcode as uuid from ' . $this->tablename . ' where batchcode in   ( ' . $inFlag . ' )';
        $case = Db::select($sql2, $uuids);
        $insertedCaseUids = array_column($case, 'uuid');
        foreach ($case as $value) {
            $caseArr[$value['uuid']] = $value['caseid'];
        }
        $val2 = $values = [];
        $sql2 = 'insert into CaseOperator (subid,projectid,caseid,oprid,status,isowner,issys,createdate,updatedate) values';
        foreach ($params as $param) {
            //对excel每一行数据进行判断(每一行可能会分解成多个职位)，如果该行有职位插入失败，则返回失败信息
            $errorFlag = false;
            $errorCaseArea = [];
            foreach ($param['uuids'] as $key => $caseUuid) {
                if (!in_array($caseUuid, $insertedCaseUids)) {
                    array_push($errorCaseArea, $param['area'][$key]);
                    $insertedCaseUids = array_diff($insertedCaseUids, $caseUuid);
                    $errorFlag = true;
                }
            }
            if ($errorFlag == true) {
                $errorList = [
                    'No' => $param['No'],
                    'errorMsg' => '插入职位' . implode(',', $errorCaseArea) . '失败'
                ];
                continue;
            }

            $values = '(?,?,?,?,?,?,?,NOW(),NOW())';
            $isowner = $issys = 0;
            if ($param['separate'] == false) {
                foreach ($param['oprid'] as $oprid) {
                    $val = [
                        $param['subid'],
                        $param['projectid'],
                        $caseArr[$param['uuids'][0]],
                        $oprid,
                        '01',
                        $isowner,
                        $issys,
                    ];
                    $val2 = array_merge($val2, $val);
                    $sql2 .= $values . ',';
                }
            } else {
                foreach ($param['uuids'] as $key => $uuid) {
                    foreach ($param['oprid'] as $oprid) {
                        $val = [
                            $param['subid'],
                            $param['projectid'],
                            $caseArr[$uuid],
                            $oprid,
                            '01',
                            $isowner,
                            $issys,
                        ];
                        $val2 = array_merge($val2, $val);
                        $sql2 .= $values . ',';
                    }
                }
            }
        }

        $sql2 = substr($sql2, 0, -1);
        Db::insert($sql2, $val2);
        $inFlag = substr(str_repeat('? ,', (count($insertedCaseUids))), 0, -1);
        $sql3 = 'select caseid,casename from ' . $this->tablename . ' where batchcode in   ( ' . $inFlag . ' )';
        $insertCases = Db::select($sql3, $insertedCaseUids);
        return [
            'errorlist' => $errorList,
            'insertcases' => $insertCases
        ];
    }

    public function getInfoByCaseId($oprId, $caseId) {
        $sql = 'select oprid from CaseOperator where caseid=? and status=?';
        $var = [$caseId, '01'];
        $result = Db::select($sql, $var);
        $oprIds = [];
        foreach ($result as $opr) {
            $oprIds[] = $opr['oprid'];
        }
        if (!in_array($oprId, $oprIds)) { // 校验操作人是否为职位协作人
            return [];
        }
        $sql = 'select c.subid,subname,c.projectid,projectname,caseid,hascontact,haschargeway,casename,caseshowname,c.status,area,salaryfrom,salaryto,content,listindex,c.createdate,duedate,vacancy from ' . $this->tablename . ' as c left join Project as p on c.projectid = p.projectid left join  CustomerSub as s on s.subid = c.subid where c.caseid = ? limit ?';
        return Db::selectOne($sql, [$caseId, 1]);
    }

    public function search($search = [], $field = '', $rowFrom = 0, $rowTo = 0, $join = false, $orderBy = '') {
        $sql = 'select ' . $field . ' from ' . $this->tablename . ' as c';
        $where = $var = [];
        if (isset($search['casename']) && !empty($search['casename'])) {
            $caseNameOrId = $this->searchStringOrNum($search['casename'], 'casename', 'caseid');
            $where[] = $caseNameOrId['where'];
            $var = array_merge($var, $caseNameOrId['var']);
        }
        if (isset($search['caseids']) && !empty($search['caseids'])) {
            $inFlag = substr(str_repeat('? ,', (count($search['caseids']))), 0, -1);
            $where [] = ' c.caseid in ( ' . $inFlag . ' )';
            $var = array_merge($var, $search['caseids']);
        }
        if (isset($search['projectids']) && !empty($search['projectids'])) {
            $inFlag = substr(str_repeat('? ,', (count($search['projectids']))), 0, -1);
            $where [] = ' c.projectid in ( ' . $inFlag . ' )';
            $var = array_merge($var, $search['projectids']);
        }
        if (isset($search['isfinished']) && $search['isfinished'] == 1) {
            $where [] = 'c.status = ?';
            $var[] = '03';
        } else if (isset($search['status']) && $search['status']) {
            $where [] = 'c.status = ?';
            $var[] = $search['status'];
        }
        if (isset($search['listindex']) && $search['listindex']) {
            $where [] = 'c.listindex = ?';
            $var[] = $search['listindex'];
        }
        if ($join) {
            $sql .= ' left join Project on c.projectid = Project.projectid';
        }
        if (!empty($where)) {
            $sql .= ' WHERE ' . implode(' AND ', $where);
        }

        $sql .= empty($orderBy) ? ' ORDER BY caseid DESC' : (' ORDER BY ' . $orderBy);
        return Db::select($sql, $var, $rowFrom, $rowTo);
    }

    /**
     * 根据项目id获取项目下职位列表， 有限制一个项目下最多只有一百个职位，所以不做分页处理，所有查询由前端js聚合即可
     * @param $oprId
     * @param $projectId
     * @return array
     */
    public function getCaseListByProjectId($oprId, $projectId) {
        $sql = 'SELECT a.caseid,a.casename,a.projectid,a.status,a.listindex FROM SCase a LEFT JOIN CaseOperator b on a.caseid=b.caseid WHERE b.status=? AND b.oprid=? AND a.projectid=? ORDER BY listindex DESC ,caseid DESC ';
        $var = ['01', $oprId, $projectId];
        $caseList = Db::select($sql, $var);
        $caseIds = array_column($caseList, 'caseid');
        $caseTags = $this->getCaseResumeCountReport($caseIds);
        foreach ($caseList as &$item) {
            $item['caseflowcount'] = $caseTags[$item['caseid']] ?? [];
        }
        return $caseList;
    }

    public function getCaseInfoByCaseIds($caseIds) {
        $inFlags = substr(str_repeat('?,', count($caseIds)), 0, -1);
        $sql = 'select caseid,casename,projectid,subid,hascontact,haschargeway from ' . $this->tablename . ' where  caseid in ( ' . $inFlags . ' )';
        return Db::select($sql, $caseIds);
    }

    public function getProjectInfoByProjectIds($projectIds) {
        $inFlags = substr(str_repeat('?,', count($projectIds)), 0, -1);
        $sql = 'select projectid,projectname,subname from Project a left join CustomerSub b on a.subid=b.subid where projectid in(' . $inFlags . ')';
        return Db::select($sql, $projectIds);
    }


    public function edit($caseId, $oprId, $params) {
        $sql = 'select caseid from SCase where caseid = ? and status != ?';
        $var = [$caseId, '03'];
        $result = Db::selectOne($sql, $var);
        if (empty($result)) {
            return [
                'status' => false,
                'message' => '职位已结束，无法编辑',
            ];
        }
        //检查项目状态是否为进行中
        $result = $this->chkProjectStatus($caseId);
        if (empty($result) || empty($oprId)) {
            return [
                'status' => false,
                'message' => '项目非进行中，无法编辑',
            ];
        }

        $projectModel = new ProjectModel();
        $projectOpr = $projectModel->getProjectOpr($params['subid'], $params['projectid'], '1');
        if (empty($projectOpr) || count($projectOpr) < 1) {
            return [
                'status' => false,
                'message' => '此项目无负责人，无法编辑该项目',
            ];
        }

        // 项目负责人比在职位中
        if (!in_array($projectOpr[0]['oprid'], $params['oprids'])) {
            $params['oprids'][] = $projectOpr[0]['oprid'];
        }

        $sql = 'select subid,projectid,oprid from CaseOperator where caseid = ? and status = ?';
        $caseSysOpr = Db::connect()->select($sql, [$caseId, '01']);
        $delCaseOpr = [];
        if (empty($caseSysOpr) || count($caseSysOpr) < 1) {
            $addCaseOpr = $params['oprids'];
        } else {
            $dbCaseOpr = array_column($caseSysOpr, 'oprid');
            //需要删除
            $delCaseOpr = array_diff($dbCaseOpr, $params['oprids']);
            //需要新增
            $addCaseOpr = array_diff($params['oprids'], $dbCaseOpr);
        }

        try {
            return Db::connect('jyrpo_pdo')->transaction(function () use ($caseId, $oprId, $params, $addCaseOpr, $delCaseOpr) {
                //修改Scase表
                $sql = 'update ' . $this->tablename . ' set casename = ?,salaryfrom = ?,salaryto = ?,area = ?,content = ?,updator = ?,listindex = ? ,caseshowname=? ,vacancy = ? , updatedate = NOW() where caseid = ?';
                $var = [
                    $params['casename'],
                    $params['salaryfrom'],
                    $params['salaryto'],
                    $params['area'],
                    $params['content'],
                    $oprId,
                    $params['listindex'],
                    $params['caseshowname'],
                    $params['vacancy'],
                    $caseId
                ];
                Db::connect('jyrpo_pdo')->update($sql, $var);

                //删除多余数据
                if (!empty($delCaseOpr)) {
                    $delCaseOpr = array_unique($delCaseOpr);
                    $inFlag = substr(str_repeat('?,', count($delCaseOpr)), 0, -1);
                    $sql = 'update CaseOperator set status = ? where caseid = ? and status = ? and oprid in (' . $inFlag . ')';
                    Db::connect('jyrpo_pdo')->update($sql, array_merge(['06', $caseId, '01'], $delCaseOpr));
                }

                //插入新数据
                if (!empty($addCaseOpr)) {
                    $sql = 'insert into CaseOperator (subid,projectid,caseid,oprid,status,isowner,issys,createdate,updatedate) values';
                    $var = $values = [];
                    foreach ($addCaseOpr as $key => $caseOprId) {
                        $values[] = '(?,?,?,?,?,?,?,NOW(),NOW())';
                        $var1 = [
                            $params['subid'],
                            $params['projectid'],
                            $caseId,
                            $caseOprId,
                            '01',
                            0,
                            0,
                        ];
                        $var = array_merge($var, $var1);
                    }
                    $sql .= implode(',', $values);
                    Db::connect('jyrpo_pdo')->insert($sql, $var);
                }
                return [
                    'status' => true,
                    'message' => '修改成功',
                ];
            });
        } catch (Exception $exception) {
            return [
                'status' => false,
                'message' => '修改失败',
            ];
        }
    }

    public function editOpr($params) {
        $message = '修改成功';
        $userid = UserAuth::getLoginId();
        $sql = 'select subid,projectid,oprid from CaseOperator where caseid = ? and status = ? limit ?';
        $caseOpr = Db::connect('jyrpo_pdo')->selectOne($sql, [$params['caseid'], '01', 1]);
        if (in_array($caseOpr['oprid'], $params['oprids'])) {
            $params['oprids'] = array_diff($params['oprids'], [$caseOpr['oprid']]);
        }

        $params['subid'] = $caseOpr['subid'];
        $params['projectid'] = $caseOpr['projectid'];
        Db::connect('jyrpo_pdo')->transaction(function () use ($params, $userid) {
            $sql = 'update CaseOperator set status = ? where caseid = ? and status = ?';
            Db::connect('jyrpo_pdo')->update($sql, ['06', $params['caseid'], '01']);
            if ($params['oprids']) {
                $sql = 'insert into CaseOperator (subid,projectid,caseid,oprid,status,isowner,createdate,updatedate) values';
                $var = $values = [];
                foreach ($params['oprids'] as $key => $oprid) {
                    $values[] = '(?,?,?,?,?,?,NOW(),NOW())';
                    $var1 = [
                        $params['subid'],
                        $params['projectid'],
                        $params['caseid'],
                        $oprid,
                        '01',
                        0,
                    ];
                    $var = array_merge($var, $var1);
                }
                $sql .= implode(',', $values);
                Db::connect('jyrpo_pdo')->insert($sql, $var);
            }
        });
        return $message;
    }

    public function batchEdit($params) {
        $result = $this->chkProjectStatus($params['caseids'][0]);
        //检查项目状态是否为进行中
        if ($params['field'] != 'status') { //项目非进行状态下，职位只有status存在修改的可能
            if (empty($result)) {
                return [
                    'status' => false,
                    'message' => '项目已暂停或结束，无法编辑职位'
                ];
            }
        } else if ($params['field'] == 'status') {
            if (empty($result)) {  //如果修改职位状态,且该项目不在进行中，则职位状态只能修改为结束
                if ($params['value'] != '03') {
                    return [
                        'status' => false,
                        'message' => '项目非进行中,只能进行结束职位操作'
                    ];
                }
            }
        }
        if (!in_array($params['field'], $this->batchFields)) {
            return [
                'status' => false,
                'message' => '批量字段非法'
            ];
        }

        $inFlag = substr(str_repeat('?,', count($params['caseids'])), 0, -1);
        $sql = 'update ' . $this->tablename . ' set ' . $params['field'] . ' = ?,updator = ?,updatedate = NOW() where caseid in (' . $inFlag . ') ';
        $var = array_merge([$params['value'], $params['userid']], $params['caseids']);
        if ($params['field'] == 'status') {
            $sql .= 'and status != ?';
            $var = array_merge($var, ['03']);
        }
        $result = Db::update($sql, $var);
        return [
            'status' => $result > 0,
            'message' => $result == count($params['caseids']) ? '修改成功' : '成功' . $result . '条'
        ];
    }

    public function batchEditOpr($params, $isreplace = false) {
        if (!$params['caseids'] || !$params['oprids']) {
            return [
                'status' => false,
                'message' => '非法数据'
            ];
        }

        //检查项目状态是否为进行中
        $result = $this->chkProjectStatus($params['caseids'][0]);
        if (empty($result)) {
            return [
                'status' => false,
                'message' => '目前无法编辑该项目'
            ];
        }

        $subId = $result['subid'];
        $projectId = $result['projectid'];

        $projectModel = new ProjectModel();
        $projectOpr = $projectModel->getProjectOpr($subId, $projectId, '1');
        if (empty($projectOpr) || count($projectOpr) < 1) {
            return [
                'status' => false,
                'message' => '此项目无负责人，无法编辑该项目'
            ];
        }

        if (count($params['caseids']) > 50 || count($params['oprids']) > 50) {
            return [
                'status' => false,
                'message' => '所选职位与协作人均不能大于50条'
            ];
        }

        // 项目负责人比在职位中
        if (!in_array($projectOpr[0]['oprid'], $params['oprids'])) {
            $params['oprids'][] = $projectOpr[0]['oprid'];
        }

        try {
            return Db::connect('jyrpo_pdo')->transaction(function () use ($subId, $projectId, $params, $isreplace) {
                $caseOprList = $values = [];
                $inFlag1 = substr(str_repeat('?,', count($params['caseids'])), 0, -1);
                $inFlag2 = substr(str_repeat('?,', count($params['oprids'])), 0, -1);
                if ($isreplace) {
                    foreach ($params['caseids'] as $caseId) {
                        $caseOprList[$caseId] = $params['oprids'];
                    }
                } else {
                    $sql = 'select subid,projectid,caseid,oprid from CaseOperator where caseid in (' . $inFlag1 . ') and oprid in (' . $inFlag2 . ') and status = ?';
                    $allCaseOpr = Db::connect('jyrpo_pdo')->select($sql, array_merge($params['caseids'], $params['oprids'], ['01']));

                    foreach ($allCaseOpr as $caseOpr) {
                        $caseOprList[$caseOpr['caseid']][] = $caseOpr['oprid'];
                    }
                    foreach ($params['caseids'] as $caseId) {
                        if (!isset($caseOprList[$caseId])) {
                            //增补数据
                            $caseOprList[$caseId] = $params['oprids'];
                        } else {
                            //剔除数据
                            $caseOprList[$caseId] = array_diff($params['oprids'], $caseOprList[$caseId]);
                        }
                    }
                }

                $sql = 'insert into CaseOperator (subid,projectid,caseid,oprid,status,isowner,issys,createdate,updatedate) values';
                $var = [];
                foreach ($caseOprList as $caseid => $oprids) {
                    if (!$oprids) {
                        continue;
                    }
                    foreach ($oprids as $oprid) {
                        $values[] = '(?,?,?,?,?,?,?,NOW(),NOW())';
                        $var1 = [
                            $subId,
                            $projectId,
                            $caseid,
                            $oprid,
                            '01',
                            0,
                            0
                        ];
                        $var = array_merge($var, $var1);
                    }
                }
                if (!$values) {
                    return [
                        'status' => false,
                        'message' => '所选已全部是职位协作人'
                    ];
                }
                if ($isreplace) {
                    //替换时需删除原有数据
                    $updateSql = 'update CaseOperator set status = ? where status = ? and caseid in (' . $inFlag1 . ')';
                    Db::connect('jyrpo_pdo')->update($updateSql, array_merge(['06', '01'], $params['caseids']));
                }
                if (count($values) > 1000) {
                    Log::info('批量修改职位协作人value统计:[ 共' . count($values) . '条数据插入]');
                }
                $sql .= implode(',', $values);
                Db::connect('jyrpo_pdo')->insert($sql, $var);
                return [
                    'status' => true,
                    'message' => '修改成功'
                ];
            });
        } catch (Exception $exception) {
            Log::error('批量修改职位协作人失败:[' . $exception->getMessage() . ']', $params);
            return [
                'status' => false,
                'message' => '修改失败'
            ];
        }
    }

    /////////////////////////////////////////////////////////////////
    ///  add code by ab 20190826
    /////////////////////////////////////////////////////////////////

    /**
     * 获取活跃职位总数
     * @param $oprId
     * @return mixed
     */
    public function getActiveCaseCount($oprId, $caseName = '', $projectName = '') {
        $sql = 'SELECT count(1) as count FROM SCase a ';
        if (!empty($projectName)) {
            $sql .= 'left join Project c on a.projectid = c.projectid ';
        }
        $where = ' WHERE a.caseid IN(SELECT caseid FROM CaseOperator WHERE oprid=? AND status=?) AND a.status=? ';
        $var = [$oprId, '01', '01'];
        if (!empty($caseName)) {
            $where .= ' and a.casename like ? ';
            $var[] = '%' . $caseName . '%';
        }
        if (!empty($projectName)) {
            $where .= ' and c.projectname like ? ';
            $var[] = '%' . $projectName . '%';
        }
        $sql .= $where;

        $result = Db::selectOne($sql, $var);
        return $result['count'];
    }

    /**
     * 获取操作人可以操作的职位列表
     * @param $oprId
     * @param $rowFrom
     * @param $rowTo
     * @return array
     */
    public function getActiveCaseList($oprId, $rowFrom, $rowTo, $caseName = '', $projectName = '') {
        $sql = 'SELECT a.caseid,a.casename,a.subid,a.projectid,hascontact,haschargeway FROM SCase a ';
        if (!empty($projectName)) {
            $sql .= 'left join Project c on a.projectid = c.projectid ';
        }
        $where = ' WHERE a.caseid IN(SELECT caseid FROM CaseOperator WHERE oprid=? AND status=?) AND a.status=? ';
        $var = [$oprId, '01', '01'];
        if (!empty($caseName)) {
            $where .= ' and a.casename like ? ';
            $var[] = '%' . $caseName . '%';
        }
        if (!empty($projectName)) {
            $where .= ' and c.projectname like ? ';
            $var[] = '%' . $projectName . '%';
        }
        $sql .= $where;
        $caseList = Db::select($sql, $var, $rowFrom, $rowTo);

        $projectIds = [];
        foreach ($caseList as $item) {
            $projectIds[] = $item['projectid'];
        }
        $projectList = [];
        if (!empty($projectIds)) {
            $inFlags = substr(str_repeat('?,', count($projectIds)), 0, -1);
            $sql = 'select projectid,projectname,subname from Project a left join CustomerSub b on a.subid=b.subid where projectid in(' . $inFlags . ')';
            $projectList = Db::connect()->select($sql, $projectIds);
        }
        return ['caselist' => $caseList, 'projectlist' => $projectList];
    }

    /**
     * 根据项目id获取所有职位数据
     * @param $oprId
     * @param $projectId
     * @param string $field
     * @param string $caseStatus
     * @param array $caseIds
     * @return array
     */
    public function getCaseListWithProjectId($oprId, $projectId, $field = '', $caseStatus = 'unfinish', array $caseIds = []) {
        if ($projectId <= 0) {
            return [];
        }

        if (empty($field)) {
            $field = 'a.caseid,a.casename,a.projectid,a.subid,a.status,a.hascontact,a.haschargeway';
        } else {
            $fieldArr = explode(',', $field);
            for ($i = 0; $i < count($fieldArr); $i++) {
                $fieldArr[$i] = 'a.' . trim($fieldArr[$i]);
            }
            $field = implode($fieldArr, ',');
        }
        if ($oprId > 0) {
            $sql = 'SELECT ' . $field . ' from SCase a LEFT JOIN CaseOperator b on a.caseid=b.caseid WHERE b.oprid=? and b.status=? and a.projectid=?';
            $var = [$oprId, '01', $projectId];
        } else {
            $sql = 'SELECT ' . $field . ' from SCase as a where a.projectid=?';
            $var = [$projectId];
        }
        if ($caseStatus == 'all') {

        } elseif ($caseStatus == 'active') {
            $sql .= ' and a.status=?';
            $var[] = '01';
        } elseif ($caseStatus == 'finish') {
            $sql .= ' and a.status=?';
            $var[] = '03';
        } else {
            $sql .= ' and a.status<>?';
            $var[] = '03';
        }
        if (!empty($caseIds)) {
            $sql .= ' AND a.caseid IN(' . substr(str_repeat('?,', count($caseIds)), 0, -1) . ')';
            $var = array_merge($var, $caseIds);
        }

        $caseList = Db::select($sql, $var, 0, 1000);
        return $caseList;
    }

    /**
     * Notes:根据项目id获取职位及其创建者信息
     * @param $oprId
     * @param $projectId
     * @param string $caseStatus
     * @return array
     */
    public function getCaseListWithCreator($oprId, $projectId, $caseStatus = 'unfinish') {
        if ($projectId <= 0) {
            return [];
        }

        $field = 'a.caseid,a.casename,a.creator,u.cname,a.projectid,a.subid,a.status,a.hascontact,a.haschargeway';

        $sql = 'SELECT ' . $field . ' from SCase a LEFT JOIN CaseOperator b on a.caseid=b.caseid left join User as u on a.creator = u.userid WHERE b.oprid=? and b.status=? and a.projectid=? ';
        $var = [$oprId, '01', $projectId];

        if ($caseStatus == 'all') {

        } elseif ($caseStatus == 'active') {
            $sql .= ' and a.status=?';
            $var[] = '01';
        } elseif ($caseStatus == 'finish') {
            $sql .= ' and a.status=?';
            $var[] = '03';
        } else {
            $sql .= ' and a.status<>?';
            $var[] = '03';
        }
        return Db::select($sql, $var, 0, 1000);
    }


    /**
     * Notes:职位id获取职位附带客户信息
     * @param array $caseIds
     * @return array
     */
    public function getCaseListWithCustomerInfo(array $caseIds) {
        $inFlags = substr(str_repeat('?,', count($caseIds)), 0, -1);
        $caseInfoSql = 'select a.caseid,a.casename,b.projectname,c.subname,a.projectid,a.subid from SCase a LEFT JOIN Project b ON a.projectid=b.projectid LEFT JOIN CustomerSub c ON a.subid=c.subid WHERE caseid in(' . $inFlags . ') ORDER BY caseid DESC';
        $caseList = Db::select($caseInfoSql, $caseIds);

        return $caseList;
    }

    /**
     * Notes:校验流程状态是否可选
     * @param $oprId
     * @param $projectId
     * @param array $caseResumeIds
     * @return array
     * @throws Exception
     */
    public function checkFlowStatus($oprId, $projectId, array $caseResumeIds) {
        $sql = 'select subid from Project where projectid = ?';
        $subId = Db::selectOne($sql, [$projectId]);
        if (!$subId) {
            return [
                'status' => false,
                'message' => '参数错误',
                'flowat' => ''
            ];
        }
        $subId = $subId['subid'];
        //根据caseresume表主键id获取简历信息，判断能够操作哪些流程节点，只是预判，所以无需添加权限校验
        $dbIndex = DbIndex::getDbIndex($subId);
        $dbName = 'jyrpo_' . $dbIndex['dbid'];
        $sql = 'SELECT id,flowat,caseid,projectid FROM CaseResume_' . $dbIndex['tableid'] . ' WHERE id IN(' . mergeArrSql($caseResumeIds) . ') AND status=? AND subid=?';
        $var = $caseResumeIds;
        $var[] = '01';
        $var[] = $subId;

        $result = Db::connect($dbName)->select($sql, $var);
        $flowAt = $caseIds = $projectIds = $existCaseResumeIds = [];
        foreach ($result as $item) {
            $existCaseResumeIds[] =
            $flowAt[] = $item['flowat'];
            $caseIds[] = $item['caseid'];
            $projectIds[] = $item['projectid'];
        }
        $flowAt = array_unique($flowAt);
        $caseIds = array_unique($caseIds);
        $projectIds = array_unique($projectIds);
        if (count($flowAt) != 1 || count($projectIds) != 1 || count($existCaseResumeIds) != count($caseResumeIds)) {
            return [
                'status' => false,
                'message' => '参数错误',
                'flowat' => ''
            ];
        }

        $caseFlow = new CaseFlow();
//        $nextFlowAt = $caseFlow->getNextFlow($subId, $flowAt[0]);
        $nextFlowAt = $caseFlow->getProjectNextFlow($projectId, $flowAt[0]);
        if ($flowAt[0] == '01') {
            //加入职位不可选
            unset($flowAt[0]);
        }
        if (!empty($nextFlowAt)) {
            $flowAt[] = $nextFlowAt;
        }

        $caseOperatorModel = new CaseOperator();
        //当前登录人无权操作或者有不是进行中职位的情况下，不可修改编辑流程状态
        if (!$caseOperatorModel->chkMultiCaseResumeOprRight($oprId, $caseIds)) {
            return [
                'status' => false,
                'message' => '非职位执行人或职位非进行中',
                'flowat' => ''
            ];
        }

        return [
            'status'  => true,
            'message' => '获取成功',
            'flowat' => $flowAt
        ];
    }

    /**
     * 获取项目下出在某节点的数目
     * @param $projectId
     * @param $flowAt
     * @return array|bool
     * @throws Exception
     */
    public function getFlowCountInProject($projectId, $flowAt) {
        $projectModel = new ProjectModel();
        $projectInfo = $projectModel->getProjectInfo($projectId, 'subid');
        $subId = $projectInfo['subid'] ?? 0;
        if (!$subId) {
            return false;
        }
        $dbIndex = DbIndex::getDbIndex($subId);
        $dbName = 'jyrpo_' . $dbIndex['dbid'];

        $sql = 'SELECT caseid,count(1) as count FROM CaseResume_' . $dbIndex['tableid'] . ' WHERE subid=? AND projectid=? AND flowat=? AND status=? group by caseid';
        $var = [$subId, $projectId, $flowAt, '01'];

        $result = Db::connect($dbName)->select($sql, $var);
        $caseIds = [];
        $caseItem = [];
        foreach ($result as $item) {
            $caseIds[] = $item['caseid'];
            $caseItem[$item['caseid']] = [
                'count' => $item['count'],
                'caseid' => $item['caseid'],
            ];
        }
        if (empty($caseIds)) {
            return [];
        }
        $caseInfoList = $this->getCaseList($caseIds, 'caseid,casename');
        foreach ($caseInfoList as $item) {
            $caseItem[$item['caseid']]['casename'] = $item['casename'];
        }
        return $caseItem;
    }

    /**
     * 简历加入职位
     * @param $oprId  //操作人ID
     * @param $caseId  //职位id
     * @param array $resumeIds //简历id
     * @return array
     * @throws Exception
     */
    public function addToCase($oprId, int $caseId, array $resumeIds) {
       $userModel = new UserModel(); //实例化用户对象
        $oprInfo = $userModel->getInfo($oprId, 'cname,teamid,teamcode');
        //	根据操作人id查询操作人信息cname|姓名,teamid|团队id,teamcode|团队code
        //step1: 确认操作人是否有职位的操作权限
        $resumeIds = array_filter($resumeIds); //过滤 0和空得简历id
        if (empty($resumeIds)) { //如果没有简历
            return [
                'status' => -1,
                'message' => '请选择简历',
            ];
        }
        if (count($resumeIds) > 50) { //如果简历>50
            return [
                'status' => -1,
                'message' => '最多支持批量50份简历',
            ];
        }
        $caseOperator = new CaseOperator();//实例化简历操作人对象
        if (!$caseOperator->chkCaseOprRight($oprId, $caseId)) { //判断操作人是否具有操作权限
            return [
                'status' => -2,
                'message' => '您没有这个职位的操作权限',
            ];
        }

        //step2:获取职位对应的流程  只判断职位是否存在，不判断职位有效性
        $caseInfo = $this->getCaseInfo($caseId, 'caseid,subid,status,projectid,hascontact,haschargeway');
        //根据职位id获取职位信息：caseid|简历id，subid|交易公司id，status|职位状态，projectid|项目id，
            //hascontact|是否定义沟通记录表头，haschargeway|是否定义交付方式
        if (empty($caseInfo)) { //如果职位信息空
            return [
                'status' => -4,
                'message' =>    '您选择的不是有效职位',
            ];
        }
        if ($caseInfo['status'] != '01') { //如果职位状态不在进行中
            return [
                'status' => -4,
                'message' => '只能操作进行中的职位',
            ];
        }
        if ($caseInfo['hascontact'] == 0) {   //如果没有沟通记录表头
            return [
                'status' => -4,
                'message' => '此职位未定义沟通记录表头',
            ];
        }
        if ($caseInfo['haschargeway'] == 0) {   //如果未定义交付方式
            return [
                'status' => -4,
                'message' => '此职位未定义交付方式',
            ];
        }
        //获取客户id以获取分库信息
        $subId = $caseInfo['subid']; //交易公司id
        $dbIndex = DbIndex::getDbIndex($subId); //根据客户id计算出分库分表索引id

        /*
         * 加入职位默认标签一定要是 01 加入项目 02 被动补充
        $defaultFlow = [
            'flowat' => '01', 'flowatstatus' => '02', 'iskpi' => 1
        ];
        */

        //step2:查询出已经在职位中的简历
        $caseResumeModel = new CaseResumeModel(); //实例化
        $resumeList = $caseResumeModel->getResumeInCase([$caseId], $resumeIds, $dbIndex['dbid'], $dbIndex['tableid']);
        //获取在职位中的简历
        $existResumeList = $resumeList[$caseId] ?? [];
        $resumeIds = array_diff($resumeIds, array_column($existResumeList, 'resumeid'));

        if (empty($resumeIds)) {
            return [
                'status' => -3,
                'message' => '所选简历已经全部在职位中，无需重复添加',
                'existresumelist' => (new CaseFlow())->rowTrans($existResumeList),
                'newaddresumeids' => '',
            ];
        }


        $currentDateTime = date('Y-m-d H:i:s');

        //简历标识及历史表主键id标志
        $keyCodes = $tagHisIds = [];

        $dbName = 'jyrpo_' . $dbIndex['dbid'];

        $caseResumeInsSql = 'INSERT INTO CaseResume_' . $dbIndex['tableid'] . '(resumeid,caseid,projectid,creator,createdate,updator,updatedate,flowat,flowatstatus,subid,status,waitflow,tagadderid,tagaddername,tagadderteamid,tagadderteamcode,tagadddate,keycode,taghisid) VALUES ';
        $caseResumeInsVar = [];
        $resumeIdWithCase = [];

        $redisCaseFlowBaseInfo = ['caseid' => $caseId, 'subid' => $caseInfo['subid'], 'projectid' => $caseInfo['projectid'], 'flowat' => '01', 'flowatstatus' => '02', 'tagaddername' => $oprInfo['cname'], 'datetime' => $currentDateTime];

        foreach ($resumeIds as $resumeId) {
            //职位标签变动历史表主键id
            $tagHisId = Uuid::uuid1()->toString();
            $keyCode = Uuid::uuid1()->toString();
            $keyCodes[$resumeId] = $keyCode;
            $tagHisIds[$resumeId] = $tagHisId;
            $caseResumeInsSql .= '(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?),';
            $caseResumeInsVar = array_merge($caseResumeInsVar, [
                $resumeId, $caseId, $caseInfo['projectid'], $oprId, $currentDateTime, $oprId, $currentDateTime, '01', '02', $caseInfo['subid'], '01', 0, $oprId, $oprInfo['cname'], $oprInfo['teamid'], $oprInfo['teamcode'], $currentDateTime, $keyCode, $tagHisId
            ]);
            $resumeIdWithCase[] = $caseId . '_' . $resumeId;
        }
        $caseResumeInsSql = substr($caseResumeInsSql, 0, -1);
        $insCaseResume = Db::connect($dbName)->prepareStatement($caseResumeInsSql, $caseResumeInsVar);

        $caseResumeTagInsSql = 'INSERT INTO CaseResumeTagHis_' . $dbIndex['tableid'] . '(id,resumeid,caseid,subid,projectid,creator,creatorname,createdate,updator,updatorname,updatedate,flowat,flowatstatus,status,iskpi,oprteamcode,oprteamid,waitflow,keycode,lasthisid) VALUES ';
        $caseResumeTagInsVar = [];
        foreach ($resumeIds as $resumeId) {
            $caseResumeTagInsSql .= '(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?),';
            $caseResumeTagInsVar = array_merge($caseResumeTagInsVar, [
                $tagHisIds[$resumeId], $resumeId, $caseId, $caseInfo['subid'], $caseInfo['projectid'], $oprId, $oprInfo['cname'], $currentDateTime, $oprId, $oprInfo['cname'], $currentDateTime, '01', '02', '01', 1, $oprInfo['teamcode'], $oprInfo['teamid'], 0, $keyCodes[$resumeId], ''
            ]);
        }
        $caseResumeTagInsSql = substr($caseResumeTagInsSql, 0, -1);
        $insCaseResumeTag = Db::connect($dbName)->prepareStatement($caseResumeTagInsSql, $caseResumeTagInsVar);

        $caseResumeFlowTagInsSql = 'INSERT INTO CaseResumeFlowTag_' . $dbIndex['tableid'] . '(resumeid,caseid,subid,projectid,creator,createdate,updator,updatedate,flowat,flowatstatus,status,keycode,taghisid,tagadderteamid,tagadderteamcode) VALUES ';
        $caseResumeFlowTagInsVar = [];
        foreach ($resumeIds as $resumeId) {
            $caseResumeFlowTagInsSql .= '(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?),';
            $caseResumeFlowTagInsVar = array_merge($caseResumeFlowTagInsVar, [
                $resumeId, $caseId, $caseInfo['subid'], $caseInfo['projectid'], $oprId, $currentDateTime, $oprId, $currentDateTime, '01', '02', '01', $keyCodes[$resumeId], $tagHisIds[$resumeId], $oprInfo['teamid'], $oprInfo['teamcode']
            ]);
        }
        $caseResumeFlowTagInsSql = substr($caseResumeFlowTagInsSql, 0, -1);
        $insCaseResumeFlowTag = Db::connect($dbName)->prepareStatement($caseResumeFlowTagInsSql, $caseResumeFlowTagInsVar);

        if (!Db::connect($dbName)->prepareTransactionStatement(true, $insCaseResume, $insCaseResumeTag, $insCaseResumeFlowTag)->execute()) {
            return [
                'status' => -5,
                'message' => '系统繁忙，请稍后再试',
            ];
        }

        try {
            $errResume = $syncParams = [];

            $careResumeList = $caseResumeTagMap = [];
            //根据keycode换取对应ID主键值
            if (!empty($keyCodes)) {
                $keyCodes_Arr = [];
                foreach ($keyCodes as $resumeId => $item) {
                    if (!empty($item)) {
                        $keyCodes_Arr[] = $item;
                    }
                }
                $keyCodes_Arr = array_unique($keyCodes_Arr);
                if (!empty($keyCodes_Arr)) {
                    $inFlag = substr(str_repeat('?,', count($keyCodes_Arr)), 0, -1);
                    $sql = 'select id,resumeid,keycode,taghisid,caseid,flowat,flowatstatus,tagadderid,updatedate from CaseResume_' . $dbIndex['tableid'] . ' where keycode in (' . $inFlag . ')';
                    $result = Db::connect($dbName)->select($sql, $keyCodes_Arr);
                    foreach ($result as $value) {
                        $careResumeList[$value['keycode']] = $value;
                    }

                    $sql = 'select id,resumeid,keycode,taghisid,caseid,flowat,flowatstatus,updator,updatedate from CaseResumeFlowTag_' . $dbIndex['tableid'] . ' where keycode in (' . $inFlag . ')';
                    $result = Db::connect($dbName)->select($sql, $keyCodes_Arr);
                    foreach ($result as $value) {
                        $caseResumeTagMap[$value['keycode']] = $value;
                    }
                }
            }

            //将简历职位关系核心信息写入Redis
            foreach ($resumeIds as $resumeId) {
                $tmpInfo = $redisCaseFlowBaseInfo;
                $tmpInfo['keycode'] = $keyCodes[$resumeId];
                if (isset($careResumeList[$tmpInfo['keycode']]) && isset($careResumeList[$tmpInfo['keycode']]['id']) && $careResumeList[$tmpInfo['keycode']]['id'] > 0) {
                    $tmpInfo['id'] = $careResumeList[$tmpInfo['keycode']]['id'];
                    $result = $caseResumeModel->setResumeInCaseByRedis($resumeId, [$tmpInfo]);
                    if ($result === false) {
                        $errResume[] = $resumeId;
                    }
                } else {
                    $errResume[] = $resumeId;
                }

                //同步ES所需参数
                $syncParams[] = [
                    $keyCodes[$resumeId],
                    $careResumeList[$tmpInfo['keycode']]['id'] . '_' . $keyCodes[$resumeId],
                    '01',
                    1,
                    $caseInfo['projectid'],
                    $careResumeList[$tmpInfo['keycode']]['caseid'],
                    $careResumeList[$tmpInfo['keycode']]['flowat'],
                    $careResumeList[$tmpInfo['keycode']]['flowatstatus'],
                    $careResumeList[$tmpInfo['keycode']]['tagadderid'],
                    $resumeId,
                    date('Y-m-d H:i:s'),
                    $oprId,
                    $careResumeList[$tmpInfo['keycode']]['updatedate'],
                ];

                $syncParams[] = [
                    $keyCodes[$resumeId],
                    $caseResumeTagMap[$tmpInfo['keycode']]['id'] . '_' . $keyCodes[$resumeId],
                    '01',
                    2,
                    $caseInfo['projectid'],
                    $caseResumeTagMap[$tmpInfo['keycode']]['caseid'],
                    $caseResumeTagMap[$tmpInfo['keycode']]['flowat'],
                    $caseResumeTagMap[$tmpInfo['keycode']]['flowatstatus'],
                    $caseResumeTagMap[$tmpInfo['keycode']]['updator'],
                    $resumeId,
                    date('Y-m-d H:i:s'),
                    0,
                    $caseResumeTagMap[$tmpInfo['keycode']]['updatedate']
                ];
            }
            if (count($errResume) > 0) {
                Log::error('加入职位,简历写入职位关联关系失败:', [
                    'caseid' => $caseId,
                    'oprid' => $oprId,
                    'resumeIds' => $errResume,
                    'redisCaseFlowBaseInfo' => $redisCaseFlowBaseInfo,
                    'keyCodes' => $keyCodes
                ]);
            }
        } catch (Exception $e) {
            Log::error('加入职位,简历写入职位关联关系失败:[' . $e->getMessage() . ']', [
                'caseid' => $caseId,
                'oprid' => $oprId,
                'resumeIds' => $resumeIds,
                'redisCaseFlowBaseInfo' => $redisCaseFlowBaseInfo
            ]);
        }

        try {
            $this->modifyCaseResumeCountReport($caseId, '01', '02', $resumeIds);
        } catch (Exception $e) {
            Log::error('修改redis中职位简历情况出错:[' . $e->getMessage() . ']', [
                'caseid' => $caseId,
                'type' => '加入职位',
                'resumeid' => $resumeIds,
                'flowat' => '0102'
            ]);
        }

        try {
            (new CaseResume())->syncCaseFlowWithES($syncParams);
        } catch (Exception $e) {
            Log::error('推送ES流程状态失败:[' . $e->getMessage() . ']', $syncParams);
        }

        try {
            $this->modifyRecentCase($oprId, $caseId);
        } catch (Exception $e) {
            Log::error('修改redis中最近职位:[' . $e->getMessage() . ']', [
                'caseid' => $caseId,
                'oprid' => $oprId,
            ]);
        }

        //CaseResume表新增记录，需要将resumeid同步到es引擎
        $resumeUpdate = new ResumeUpdate();
        $resumeUpdate->addToQueue($resumeIds);

        return [
            'status' => 0,
            'message' => '操作成功',
            'existresumelist' => (new CaseFlow())->rowTrans($existResumeList),
            'newaddresumeids' => implode(',', $resumeIds),
        ];
    }

    /**
     * 精英简历加入职位中
     * @param $oprId
     * @param int $caseId
     * @param array $managerIds
     * @return array
     * @throws Exception
     */
    public function addJyResumeToCase($oprId, int $caseId, array $managerIds) {
        if (empty($managerIds)) {
            return [
                'status' => -1,
                'message' => '请传入有效的精英网简历ID',
            ];
        }
        //根据精英的managerid查询对应的resumeid即s_manager中的
        $managerModel = new Manager();
        $resumeList = $managerModel->getManagerListByMgrId($managerIds, 'managerid,userid51job as resumeid');

        $resumeIds = [];
        foreach ($resumeList as $item) {
            $resumeIds[$item['resumeid']] = $item['managerid'];
        }
        unset($resumeList);
        if (empty($resumeIds)) {
            return [
                'status' => -1,
                'message' => '请传入有效的精英网简历ID',
            ];
        }

        $result = $this->addToCase($oprId, $caseId, array_keys($resumeIds));
        if ($result['status'] != 0 && $result['status'] != -3) {
            return $result;
        }
        if (isset($result['newaddresumeids']) && !empty($result['newaddresumeids'])) {
            $newAddResumeIds = explode(',', $result['newaddresumeids']);
            $newAddMgrIds = [];
            foreach ($newAddResumeIds as $newAddResumeId) {
                $newAddMgrIds[] = $resumeIds[$newAddResumeId];
            }
            $result['newaddresumeids'] = implode(',', $newAddMgrIds);
        }

        if (isset($result['existresumelist']) && !empty($result['existresumelist'])) {
            foreach ($result['existresumelist'] as &$item) {
                $item['resumeid'] = $resumeIds[$item['resumeid']];
            }
        }

        return $result;
    }

    /**
     * 添加职位标签
     * @param $oprId
     * @param $subId
     * @param array $caseResumeIds
     * @param $tagCode
     * @param string $timeControl
     * @param string $memo
     * @param array $taskParams
     * @param bool $oprByCustomerFeed
     * @param bool $noTask
     * @param int $salary
     * @return array
     * @throws Throwable
     */
    public function addResumeTag($oprId, $subId, array $caseResumeIds, $tagCode, $timeControl = '', $memo = '', array $taskParams = [], $oprByCustomerFeed = false, $noTask = false, $salary = 0) {
        if (!Validation::validateMaxLength('tagmemo', $memo, 200)) {
            return ['status' => -22, 'message' => '参数校验失败', 'validateFailure' => ['tagmemo' => '标签备注最多只能输入200个字']];
        }
        if ($salary > 2000000000 || $salary < 0) {
            return ['status' => -22, 'message' => '参数校验失败', 'validateFailure' => ['salary' => '薪资不得小于0或大于2000000000']];
        }
        $dbIndex = DbIndex::getDbIndex($subId);
        $dbName = 'jyrpo_' . $dbIndex['dbid'];

        //查询简历信息
        $field = 'id,resumeid,caseid,flowat,flowatstatus,subid,timecontrol,projectid,keycode,taghisid';
        $sql = 'SELECT ' . $field . ' FROM CaseResume_' . $dbIndex['tableid'] . ' WHERE id IN(' . mergeArrSql($caseResumeIds) . ') AND status=? AND subid=?';
        $var = $caseResumeIds;
        $var[] = '01';
        $var[] = $subId;
        $resumeList = Db::connect($dbName)->select($sql, $var);
        if (empty($resumeList)) {
            return [
                'status' => -1,
                'message' => '请选择简历'
            ];
        }

        $resumeInfoList = $flowAt = $caseIds = $projectIds = $keyCodes = $resumeIdInCase = [];
        foreach ($resumeList as $key => $item) {
            if ($oprByCustomerFeed && !in_array($item['flowat'], ['03', '05'])) {
                //客户反馈，只有当前标签是：【简历推荐-03】或【简历反馈-05】时才能进行后续标签变动
                continue;
            }
            $resumeInfoList[$item['id']] = $item;
            $keyCodes[] = $item['keycode'];
            $flowAt[] = $item['flowat'];
            $caseIds[] = $item['caseid'];
            $projectIds[] = $item['projectid'];
            //记录各职位下简历id，用于后续更新redis中职位数据
            $resumeIdInCase[$item['caseid']][] = $item['resumeid'];

        }

        if (empty($resumeInfoList)) {
            if ($oprByCustomerFeed) {
                return ['status' => -21, 'message' => '没有客户可以代处理的简历'];
            } else {
                return ['status' => -1, 'message' => '请选择简历'];
            }
        }
        $caseIds = array_unique($caseIds);
        $projectIds = array_unique($projectIds);
        $flowAt = array_unique($flowAt);
        if (count($flowAt) != 1 || count($projectIds) != 1 || count($resumeInfoList) != count($caseResumeIds)) {
            //这种情况表示用户选择了不应该被选中的标签，可能是因为系统页面迟旧导致，前端可以主动刷新页面
            return [
                'status' => -10,
                'message' => '系统出现错误，无法编辑标签',
            ];
        }
        //实际操作的简历id
        $caseResumeIds = array_keys($resumeInfoList);
        $currentFlowAt = $flowAt[0];
        $projectId = $projectIds[0];
        unset($flowAt, $projectIds, $resumeList);

        $selectedFlowAt = substr($tagCode, 0, 2);
        $selectedFlowAtStatus = substr($tagCode, 2);

        $userModel = new UserModel();
        $oprInfo = $userModel->getInfo($oprId, 'userid,cname,teamid,teamcode');
        $currentDateTime = date('Y-m-d H:i:s');

        if ($selectedFlowAt != $currentFlowAt || $selectedFlowAt == '01') {
            //不可选择使用加入项目标签
            $caseFlow = new CaseFlow();
//            $nextFlowAt = $caseFlow->getNextFlow($subId, $currentFlowAt);
            $nextFlowAt = $caseFlow->getProjectNextFlow($projectId, $currentFlowAt);
            if ($nextFlowAt != $selectedFlowAt) {
                //标签超出可以选择的范围，返回错误，前端可以主动刷新页面
                return [
                    'status' => -10,
                    'message' => '系统出现错误，无法编辑标签',
                ];
            }
        }

        $caseOperatorModel = new CaseOperator();
        //当前登录人无权操作或者有不是进行中职位的情况下，不可修改编辑流程状态
        if (!$caseOperatorModel->chkMultiCaseResumeOprRight($oprId, $caseIds)) {
            return [
                'status' => -10,
                'message' => '系统出现错误，无法编辑标签',
            ];
        }
        //offer-已接受 与 入职-已入职 CaseResume表更新薪资字段，否则不更新
        $salaryUpdate = in_array($tagCode, ['1201', '1502']);
        //获取到流程信息
        $caseFlowModel = new CaseFlow();
        $flowInfo = $caseFlowModel->getFlowInfo($selectedFlowAt, $selectedFlowAtStatus);


        if ($flowInfo['hastime']) {
            if (empty($timeControl) || !Validation::ValidateDateFormat('timecontrol', $timeControl, 'Y-m-d H:i:s')) {
                return [
                    'status' => -7,
                    'message' => '请选择标签关联时间',
                ];
            }
        } else {
            $timeControl = '';
        }
        $hasTimeControl = empty($timeControl) ? false : true;

        if ($flowInfo['task'] == '00' && !$oprByCustomerFeed && !$noTask) {
            $recommendModel = new ResumeRecommend();
            $validateResult = $recommendModel->recommendParamsValidate($taskParams);
            if (!$validateResult['status']) {
                return ['status' => -22, 'message' => '参数校验失败', 'validateFailure' => $validateResult['errorFields']];
            }
        } elseif ($flowInfo['task'] == '06' && !$oprByCustomerFeed && !$noTask) {
            //电话任务
            $taskModel = new Task();
            $validateResult = $taskModel->taskParamsValidate($taskParams);
            if (!$validateResult['status']) {
                return ['status' => -22, 'message' => '参数校验失败', 'validateFailure' => $validateResult['errorFields']];
            }
        }

        $caseResumeTableName = 'CaseResume_' . $dbIndex['tableid'];
        $caseResumeTagHisTableName = 'CaseResumeTagHis_' . $dbIndex['tableid'];
        $caseResumeFlowTagTableName = 'CaseResumeFlowTag_' . $dbIndex['tableid'];

        $batchSqlStatements = [];

        //写入标签历史表
        $caseResumeTagInsSql = 'INSERT INTO ' . $caseResumeTagHisTableName . '(id,resumeid,caseid,subid,projectid,creator,creatorname,createdate,updator,updatorname,updatedate,flowat,flowatstatus,status,iskpi,oprteamcode,oprteamid,waitflow,addbycustomer,memo,keycode,lasthisid,timecontrol,salary) VALUES ';
        $caseResumeTagInsVar = [];
        //将标签下最新流程写入
        $caseResumeFlowTagInsSql = 'INSERT INTO ' . $caseResumeFlowTagTableName . '(resumeid,caseid,subid,projectid,creator,createdate,updator,updatedate,flowat,flowatstatus,status,keycode,memo,taghisid,salary,tagadderteamid,tagadderteamcode) VALUES ';
        $caseResumeFlowTagInsVar = [];
        foreach ($caseResumeIds as $caseResumeId) {
            $tagHisId = Uuid::uuid1()->toString();
            //更新CaseResume表
            $caseResumeUpdSql = 'UPDATE ' . $caseResumeTableName . ' SET flowat=?,flowatstatus=?,waitflow=?,updator=?,updatedate=?,tagadderid=?,tagaddername=?,tagadderteamid=?,tagadderteamcode=?,tagadddate=Now(),addbycustomer=?,memo=?,taghisid=?' . ($salaryUpdate ? ',salary = ?' : '') . ',timecontrol=' . ($hasTimeControl ? '?' : 'NULL') . ' WHERE id =? AND taghisid=?';
            $caseResumeUpdVar = array_merge([$selectedFlowAt, $selectedFlowAtStatus, $flowInfo['waitflow'] ? 1 : 0, $oprInfo['userid'], $currentDateTime, $oprInfo['userid'], $oprInfo['cname'], $oprInfo['teamid'], $oprInfo['teamcode'], $oprByCustomerFeed ? 1 : 0, $memo, $tagHisId], $salaryUpdate ? [$salary] : [], $hasTimeControl ? [$timeControl] : [], [$caseResumeId, $resumeInfoList[$caseResumeId]['taghisid']]);
            $batchSqlStatements[] = Db::connect($dbName)->prepareStatement($caseResumeUpdSql, $caseResumeUpdVar);

            //处理CaseResumeTagHis表
            $caseResumeTagInsSql .= '(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,' . ($hasTimeControl ? '?' : 'NULL') . ',?),';
            $caseResumeTagInsVar = array_merge($caseResumeTagInsVar, [
                $tagHisId, $resumeInfoList[$caseResumeId]['resumeid'], $resumeInfoList[$caseResumeId]['caseid'], $subId, $projectId, $oprId, $oprInfo['cname'], $currentDateTime, $oprId, $oprInfo['cname'], $currentDateTime, $selectedFlowAt, $selectedFlowAtStatus, '01', $flowInfo['iskpi'] ? 1 : 0, $oprInfo['teamcode'], $oprInfo['teamid'], $flowInfo['waitflow'] ? 1 : 0, $oprByCustomerFeed ? 1 : 0, $memo, $resumeInfoList[$caseResumeId]['keycode'], $resumeInfoList[$caseResumeId]['taghisid']
            ], $hasTimeControl ? [$timeControl] : [], [$salary]);

            //处理CaseResumeFlowTag表
            $caseResumeFlowTagInsSql .= '(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?),';
            $caseResumeFlowTagInsVar = array_merge($caseResumeFlowTagInsVar, [
                $resumeInfoList[$caseResumeId]['resumeid'], $resumeInfoList[$caseResumeId]['caseid'], $subId, $projectId, $oprId, $currentDateTime, $oprId, $currentDateTime, $selectedFlowAt, $selectedFlowAtStatus, '01', $resumeInfoList[$caseResumeId]['keycode'], $memo, $tagHisId, $salary, $oprInfo['teamid'], $oprInfo['teamcode']
            ]);
        }
        //生成批量修改CaseResume表记录操作
        $caseResumeTagInsSql = substr($caseResumeTagInsSql, 0, -1);
        $batchSqlStatements[] = Db::connect($dbName)->prepareStatement($caseResumeTagInsSql, $caseResumeTagInsVar);

        //删除在同一流程下的旧标签
        $caseResumeFlowTagUpdSql = 'DELETE FROM ' . $caseResumeFlowTagTableName . ' WHERE keycode IN(' . mergeArrSql($keyCodes) . ') AND status=? AND flowat=?';
        $caseResumeFlowTagUpdVar = array_merge($keyCodes, ['01', $selectedFlowAt]);
        $batchSqlStatements[] = Db::connect($dbName)->prepareStatement($caseResumeFlowTagUpdSql, $caseResumeFlowTagUpdVar);
        //生成批量新增CaseResumeFlowTag表记录操作
        $caseResumeFlowTagInsSql = substr($caseResumeFlowTagInsSql, 0, -1);
        $batchSqlStatements[] = Db::connect($dbName)->prepareStatement($caseResumeFlowTagInsSql, $caseResumeFlowTagInsVar);

        //执行事务
        if (!Db::connect($dbName)->prepareTransactionStatement(false, ...$batchSqlStatements)->execute()) {
            return [
                'status' => -9,
                'message' => '系统繁忙，请稍后再试',
            ];
        }


        $errors = $exceptionMessages = [];
        foreach ($resumeIdInCase as $indexCaseId => $indexResumeIds) {
            try {
                $this->modifyCaseResumeCountReport($indexCaseId, $selectedFlowAt, $selectedFlowAtStatus, $indexResumeIds);
            } catch (Exception $e) {
                $errors[$indexCaseId] = $indexResumeIds;
                $exceptionMessages[$indexCaseId] = $e->getMessage();
            }
        }
        if (!empty($errors)) {
            Log::error('修改redis中职位简历情况出错', [
                'type' => '修改简历标签',
                'flowat' => $tagCode,
                'errorresumeids' => $errors,
                'exceptionMessages' => $exceptionMessages,
            ]);
        }

        //客户反馈代操作标签，不发起任务
        if ($flowInfo['task'] == '00' && !$oprByCustomerFeed && !$noTask) {
            //简历推荐
            $taskResumeIds = [];
            foreach ($caseResumeIds as $caseResumeId) {
                $taskResumeIds[] = [
                    'caseid' => $resumeInfoList[$caseResumeId]['caseid'],
                    'resumeid' => $resumeInfoList[$caseResumeId]['resumeid'],
                    'caseresumeid' => $resumeInfoList[$caseResumeId]['id'],
                ];
            }
            $resumeRecommendModel = new ResumeRecommend();
            $addResult = $resumeRecommendModel->addRecommendTask($oprId, $oprInfo['cname'], $subId, $taskResumeIds, $taskParams);
            if (!$addResult['status']) {
                Log::error('修改简历标签添加简历推荐出错：[' . $addResult['message'] . ']', ['resumeids' => $taskResumeIds, 'addresult' => $addResult]);
            }
        } elseif ($flowInfo['task'] == '06' && !$oprByCustomerFeed && !$noTask) {
            //电话任务
            $taskModel = new Task();
            $taskResumeIds = [];
            foreach ($caseResumeIds as $caseResumeId) {
                $taskResumeIds[] = [
                    'caseid' => $resumeInfoList[$caseResumeId]['caseid'],
                    'resumeid' => $resumeInfoList[$caseResumeId]['resumeid'],
                ];
            }
            $taskParams['resumeid'] = json_encode($taskResumeIds);
            $addResult = $taskModel->saveTaskInfo($taskParams);
            if (!$addResult['status']) {
                Log::error('修改简历标签添加任务出错：[' . $addResult['message'] . ']', ['resumeids' => $taskResumeIds, 'addresult' => $addResult]);
            }
        }

        try {
            $caseResumeModel = new CaseResumeModel();
            $errResume = [];

            //将简历职位关系核心信息写入Redis
            foreach ($caseResumeIds as $caseResumeId) {
                $redisCaseFlowBaseInfo = [
                    'caseid' => $resumeInfoList[$caseResumeId]['caseid'],
                    'keycode' => $resumeInfoList[$caseResumeId]['keycode'],
                    'id' => $resumeInfoList[$caseResumeId]['id'],
                    'subid' => $subId,
                    'projectid' => $projectId,
                    'flowat' => $selectedFlowAt,
                    'flowatstatus' => $selectedFlowAtStatus,
                    'tagaddername' => $oprInfo['cname'],
                    'datetime' => $currentDateTime
                ];
                $result = $caseResumeModel->setResumeInCaseByRedis($resumeInfoList[$caseResumeId]['resumeid'], [$redisCaseFlowBaseInfo]);
                if ($result === false) {
                    $redisCaseFlowBaseInfo['resumeid'] = $resumeInfoList[$caseResumeId]['resumeid'];
                    $redisCaseFlowBaseInfo['caseresumeid'] = $caseResumeId;
                    $errResume[] = $redisCaseFlowBaseInfo;
                }
            }
            if (count($errResume) > 0) {
                Log::error('标签修改,简历写入职位关联关系失败:', $errResume);
            }
        } catch (Exception $e) {
            Log::error('标签修改,简历写入职位关联关系失败:[' . $e->getMessage() . ']');
        }

        return [
            'status' => 1,
            'message' => '操作成功',
        ];
    }

    /**
     * 标签撤销
     * @param $oprId
     * @param $oprName
     * @param $subId
     * @param $caseId
     * @param $keyCode
     * @param $tagHisId
     * @return array
     * @throws Exception
     */
    public function     removeResumeTag($oprId, $oprName, $subId, $caseId, $keyCode, $tagHisId) {
        //校验职位操作权限
        $caseOperator = new CaseOperator();
        if (!$caseOperator->chkCaseOprRight($oprId, $caseId)) {
            return [
                'status' => -1,
                'message' => '您没有这个职位的操作权限',
            ];
        }

        //根据taghisid查询标签信息
        $dbIndex = DbIndex::getDbIndex($subId);
        $dbName = 'jyrpo_' . $dbIndex['dbid'];

        //根据keycode判断能够进行回退操作
        $sql = 'SELECT id,projectid,caseid,resumeid,keycode,taghisid,caseid,flowat,flowatstatus,tagadderid,updatedate FROM ' . 'CaseResume_' . $dbIndex['tableid'] . ' WHERE keycode=? AND status=? AND caseid=?';
        $var = [$keyCode, '01', $caseId];
        $caseResumeInfo = Db::connect($dbName)->selectOne($sql, $var);
        if (empty($caseResumeInfo) || $caseResumeInfo['taghisid'] != $tagHisId) {
            return [
                'status' => -10,
                'message' => '流程状态不匹配，无法撤销',
            ];
        }


        $caseResumeTagHisTableName = 'CaseResumeTagHis_' . $dbIndex['tableid'];
        $hisTagInfoField = 'a.keycode,a.resumeid,a.caseid,a.projectid,a.id AS curid,a.flowat AS curflowat,a.flowatstatus AS curflowatstatus,a.salary AS cursalary,b.id AS preid,b.creator AS tagadderid,b.creatorname AS tagaddername,b.oprteamid AS tagadderteamid,b.oprteamcode AS tagadderteamcode,b.createdate AS tagadddate,b.flowat AS preflowat,b.flowatstatus AS preflowatstatus,b.timecontrol AS pretimecontrol,b.waitflow AS prewaitflow,b.addbycustomer AS preaddbycustomer,b.memo AS prememo,b.salary AS presalary';
        $hisTagInfoTable = $caseResumeTagHisTableName . ' a LEFT JOIN ' . $caseResumeTagHisTableName . ' b ON a.lasthisid = b.id ';
        $hisTagInfoSql = 'SELECT ' . $hisTagInfoField . ' FROM ' . $hisTagInfoTable . ' WHERE a.id=? AND a.status=? AND b.status=?';
        $hisTagVar = [$tagHisId, '01', '01'];

        $hisTagInfo = Db::connect($dbName)->selectOne($hisTagInfoSql, $hisTagVar);
        if (empty($hisTagInfo) || $caseId != $hisTagInfo['caseid']) {
            return [
                'status' => -2,
                'message' => '未查询到相关标签记录',
            ];
        }
        if ($hisTagInfo['curflowat'] . $hisTagInfo['curflowatstatus'] == '0302') {
            return [
                'status' => -3,
                'message' => '简历推荐-已推荐标签不能撤销',
            ];
        }

        $sqlStatements = [];
        $currentTime = date('Y-m-d H:i:s');
        //删除历史记录表 CaseResumeTagHis
        $caseResumeTagHisSql = 'UPDATE ' . $caseResumeTagHisTableName . ' SET status=?,updator=?,updatorname=?,updatedate=NOW() WHERE id=? AND status=?';
        $caseResumeTagHisVar = ['06', $oprId, $oprName, $tagHisId, '01'];
        $sqlStatements[] = Db::connect($dbName)->prepareStatement($caseResumeTagHisSql, $caseResumeTagHisVar);

        //处理CaseResumeFlowTag表，判定是需要删除还是更新记录
        $caseResumeFlowTagTableName = 'CaseResumeFlowTag_' . $dbIndex['tableid'];
        if ($hisTagInfo['curflowat'] == $hisTagInfo['preflowat']) {
            //最近两条历史记录一级标签相同，则需要把最新的标签数据更新进去
            $caseResumeFlowTagSql = 'UPDATE ' . $caseResumeFlowTagTableName . ' SET updator=?,updatedate=?,flowat=?,flowatstatus=?,memo=?,taghisid=?,salary=? WHERE taghisid=? AND keycode=? AND status=?';
            $caseResumeFlowTagVar = [$oprId, $currentTime, $hisTagInfo['preflowat'], $hisTagInfo['preflowatstatus'], $hisTagInfo['prememo'], $hisTagInfo['preid'], $hisTagInfo['presalary'], $tagHisId, $hisTagInfo['keycode'], '01'];

        } else {
            //最近两条历史记录一级标签不同，说明最新的标签已经没了，直接将该条删除即可
            $caseResumeFlowTagSql = 'UPDATE ' . $caseResumeFlowTagTableName . ' SET status=?,updator=?,updatedate=Now() WHERE taghisid=? AND keycode=? AND status=?';
            $caseResumeFlowTagVar = ['06', $oprId, $tagHisId, $hisTagInfo['keycode'], '01'];

        }
        $sqlStatements[] = Db::connect($dbName)->prepareStatement($caseResumeFlowTagSql, $caseResumeFlowTagVar);

        //offer-已接受 与 入职-已入职 CaseResume表更新薪资字段，否则不更新
        $salaryUpdate = in_array($hisTagInfo['preflowat'] . $hisTagInfo['preflowatstatus'], ['1201', '1502']);

        //处理CaseResume表
        $hasTimeControl = empty($hisTagInfo['pretimecontrol']) ? false : true;
        $caseResumeTableName = 'CaseResume_' . $dbIndex['tableid'];
        $caseResumeSql = 'UPDATE ' . $caseResumeTableName . ' SET timecontrol=' . ($hasTimeControl ? '?' : 'NULL') . ($salaryUpdate ? ',salary=? ' : '') . ',updator=?,updatedate=?,tagadderid=?,tagaddername=?,tagadderteamid=?,tagadderteamcode=?,tagadddate=?,flowat=?,flowatstatus=?,waitflow=?,addbycustomer=?,memo=?,taghisid=? WHERE taghisid=? AND keycode=? AND status=?';
        $caseResumeVar = array_merge($hasTimeControl ? [$hisTagInfo['pretimecontrol']] : [], $salaryUpdate ? [$hisTagInfo['presalary']] : [], [$oprId, $currentTime, $hisTagInfo['tagadderid'], $hisTagInfo['tagaddername'], $hisTagInfo['tagadderteamid'], $hisTagInfo['tagadderteamcode'], $hisTagInfo['tagadddate'], $hisTagInfo['preflowat'], $hisTagInfo['preflowatstatus'], $hisTagInfo['prewaitflow'], $hisTagInfo['preaddbycustomer'], $hisTagInfo['prememo'], $hisTagInfo['preid'], $tagHisId, $hisTagInfo['keycode'], '01']);
        $sqlStatements[] = Db::connect($dbName)->prepareStatement($caseResumeSql, $caseResumeVar);

        //执行事务
        if (!Db::connect($dbName)->prepareTransactionStatement(true, ...$sqlStatements)->execute()) {
            return [
                'status' => -9,
                'message' => '系统繁忙，请稍后再试',
            ];
        }

        try {
            $this->modifyCaseResumeCountReportForBack($caseId, $hisTagInfo['curflowat'], $hisTagInfo['curflowatstatus'], $hisTagInfo['preflowat'], $hisTagInfo['preflowatstatus'], [$hisTagInfo['resumeid']]);
        } catch (Exception $e) {
            Log::error('修改redis中职位简历情况出错', [
                'type' => '标签回退', 'histagid' => $tagHisId, 'exceptionMessages' => $e->getMessage(),
            ]);
        }

        try {
            $redisCaseFlowBaseInfo = [
                'id' => $caseResumeInfo['id'],
                'caseid' => $hisTagInfo['caseid'],
                'keycode' => $hisTagInfo['keycode'],
                'subid' => $subId,
                'projectid' => $hisTagInfo['projectid'],
                'flowat' => $hisTagInfo['preflowat'],
                'flowatstatus' => $hisTagInfo['preflowatstatus'],
                'tagaddername' => $oprName,
                'datetime' => $hisTagInfo['tagadddate'],
            ];

            //将简历职位关系核心信息写入Redis
            $caseResumeModel = new CaseResumeModel();
            $result = $caseResumeModel->setResumeInCaseByRedis($hisTagInfo['resumeid'], [$redisCaseFlowBaseInfo]);
            if ($result === false) {
                $redisCaseFlowBaseInfo['resumeid'] = $hisTagInfo['resumeid'];
                Log::error('标签撤回,简历写入职位关联关系失败:', $redisCaseFlowBaseInfo);
            }
        } catch (Exception $e) {
            Log::error('标签撤回,简历写入职位关联关系失败:[' . $e->getMessage() . ']');
        }

        return [
            'status' => 0,
            'message' => '操作成功',
        ];

    }

    /**
     * 客户反馈标签处理结果
     * @param $oprId
     * @param $subId
     * @param array $caseResumeIds
     * @param $feedResult
     * @param string $memo
     * @return array|bool
     * @throws Throwable
     */
    public function modifyResumeTagByFeed($oprId, $subId, array $caseResumeIds, $feedResult, $memo = '') {
        switch ($feedResult) {
            case 'ok':
                $tagCode = '0501';  //简历反馈-合格
                break;
            case 'pass':
                $tagCode = '0502';  //简历反馈-淘汰
                break;
            case 'wait':
                $tagCode = '0503';  //简历反馈-待定
                break;
            default:
                return false;
        }
        return $this->addResumeTag($oprId, $subId, $caseResumeIds, $tagCode, '', $memo, [], true, true);
    }

    /**
     * 记录操作人最近使用的职位id
     * @param $oprId
     * @param $caseId
     * @return bool
     */
    private function modifyRecentCase($oprId, $caseId) {
        $redisClient = Redis::connect('recentcase');
        $listKey = 'recentcaseoprid:' . $oprId;
        $recentCaseIds = $redisClient->lRange($listKey, 0, $this->recentCaseMax);
        //已存在提到最前
        if (in_array($caseId, $recentCaseIds)) {
            $redisClient->lRem($listKey, $caseId, 0);
            $redisClient->lPush($listKey, $caseId);
            return true;
        }
        $redisClient->lPush($listKey, $caseId);
        //超过最近职位限制时移除
        if ($redisClient->lLen($listKey) > $this->recentCaseMax) {
            $redisClient->lTrim($listKey, 0, $this->recentCaseMax - 1);
        }
        return true;
    }

    /**
     * 获取操作人最近使用的职位id
     * @param $oprId
     * @return array
     */
    public function getRecentCase($oprId) {
        $redisClient = Redis::connect('recentcase');
        $listKey = 'recentcaseoprid:' . $oprId;
        $caseIds = $redisClient->lRange($listKey, 0, $this->recentCaseMax - 1);
        if (!$caseIds) {
            return [];
        }
        $activeCaseIds = array_column($this->search(['caseids' => $caseIds, 'status' => '01'], 'caseid'), 'caseid');
        //剔除非进行中职位
        foreach ($caseIds as $key => $caseId) {
            if (!in_array($caseId, $activeCaseIds)) {
                $redisClient->lRem($listKey, $caseId, 0);
                unset($caseIds[$key]);
            }
        }
        if (!$caseIds) {
            return [];
        }
        //剔除已无权限职位
        $caseOperator = new CaseOperator();
        $isOpr = $caseOperator->chkCasesRight($oprId, $activeCaseIds);
        if (!$isOpr['hasright']) {
            foreach ($isOpr['norightcases'] as $item) {
                $redisClient->lRem($listKey, $item, 0);
            }
            $caseIds = array_diff($caseIds, $isOpr['norightcases']);
        }

        return $caseIds;
    }

    /**
     * 修改存放在redis中的职位中简历报表数据
     * @param $caseId
     * @param $flowAt
     * @param $flowAtStatus
     * @param array $resumeIds
     */
    public function modifyCaseResumeCountReport($caseId, $flowAt, $flowAtStatus, array $resumeIds) {
        $redisClient = Redis::connect('caseflowcount');
        $exclusiveLockName = 'caseflowcountlock:' . $caseId;
        if (!$redisClient->addExclusiveLock($exclusiveLockName)) {
            //添加分布式锁
            throw new RuntimeException('添加排他锁[' . $exclusiveLockName . ']失败');
        }

        //统计职位下各流程标签中包含简历数目
        $caseResumeCountInFlowKey = 'caseresumecount:' . $caseId;
        $flowAtKeys = 'flowatstatuskeys:' . $caseId . ':' . $flowAt;
        $existsResumeListKeys = $redisClient->scanSet($flowAtKeys);
        //将简历从现有流程标签中移除 并重新统计数据
        foreach ($existsResumeListKeys as $existsResumeListKey) {
            $redisClient->sRem($existsResumeListKey, ...$resumeIds);
            $hashKeyForFlowAt = substr($existsResumeListKey, -5, 2) . substr($existsResumeListKey, -2);
            $totalCount = $redisClient->sCard($existsResumeListKey);
            if ($totalCount == 0) {
                $redisClient->del($existsResumeListKey);
            }
            $redisClient->hSet($caseResumeCountInFlowKey, $hashKeyForFlowAt, $totalCount);
        }
        $resumeListKey = 'caseresumelist:' . $caseId . ':' . $flowAt . ':' . $flowAtStatus;
        //将简历写入新的流程标签
        $redisClient->sAddArray($resumeListKey, $resumeIds);
        //使用集合记录流程下有多少标签
        $redisClient->sAdd($flowAtKeys, $resumeListKey);
        $totalCount = $redisClient->sCard($resumeListKey);
        $redisClient->hSet($caseResumeCountInFlowKey, $flowAt . $flowAtStatus, $totalCount);

        //执行完成，移除排它锁
        $redisClient->removeExclusiveLock($exclusiveLockName);
    }

    /**
     * 修改标签回退导致的存放在redis中的职位中简历报表数据更新
     * @param $caseId
     * @param $fromFlowAt
     * @param $fromFlowAtStatus
     * @param $toFlowAt
     * @param $toFlowAtStatus
     * @param array $resumeIds
     */
    public function modifyCaseResumeCountReportForBack($caseId, $fromFlowAt, $fromFlowAtStatus, $toFlowAt, $toFlowAtStatus, array $resumeIds) {
        $redisClient = Redis::connect('caseflowcount');
        $exclusiveLockName = 'caseflowcountlock:' . $caseId;
        if (!$redisClient->addExclusiveLock($exclusiveLockName)) {
            //添加分布式锁
            throw new RuntimeException('添加排他锁[' . $exclusiveLockName . ']失败');
        }

        //统计职位下各流程标签中包含简历数目
        $caseResumeCountInFlowKey = 'caseresumecount:' . $caseId;

        //从源列表中移出简历id并加入到新的源列表
        $fromResumeListKey = 'caseresumelist:' . $caseId . ':' . $fromFlowAt . ':' . $fromFlowAtStatus;
        $redisClient->sRem($fromResumeListKey, ...$resumeIds);
        $totalCount = $redisClient->sCard($fromResumeListKey);
        $redisClient->hSet($caseResumeCountInFlowKey, $fromFlowAt . $fromFlowAtStatus, $totalCount);
        $toResumeListKey = 'caseresumelist:' . $caseId . ':' . $toFlowAt . ':' . $toFlowAtStatus;
        $redisClient->sAdd($toResumeListKey, ...$resumeIds);
        $totalCount = $redisClient->sCard($toResumeListKey);
        $redisClient->hSet($caseResumeCountInFlowKey, $toFlowAt . $toFlowAtStatus, $totalCount);

        //执行完成，移除排它锁
        $redisClient->removeExclusiveLock($exclusiveLockName);
    }

    /**
     * 同步resumecount报表数据
     * @param $caseId
     * @return int
     */
    public function resyncCaseResumeCountReport($caseId) {
        $sql = 'select subid from SCase where caseid = ?';
        $subId = Db::selectOne($sql, [$caseId]);
        if (!$subId) {
            return -1;
        }
        $subId = $subId['subid'];
        $dbIndex = DbIndex::getDbIndex($subId);
        $dbName = 'jyrpo_' . $dbIndex['dbid'];
        $sql = 'SELECT resumeid,caseid,flowat,flowatstatus FROM CaseResumeFlowTag_' . $dbIndex['tableid'] . ' where caseid=? AND status=?';
        $var = [$caseId, '01'];
        $list = Db::connect($dbName)->select($sql, $var);
        if (empty($list)) {
            return -1;
        }
        $caseResumeCount = $caseResumeList = [];
        foreach ($list as $item) {
            $caseResumeCount[$item['flowat'] . $item['flowatstatus']] = ($caseResumeCount[$item['flowat'] . $item['flowatstatus']] ?? 0) + 1;
            $caseResumeList[$item['flowat']][$item['flowatstatus']][] = $item['resumeid'];
        }
        unset($list);
        $redisClient = Redis::connect('caseflowcount');
        $exclusiveLockName = 'caseflowcountlock:' . $caseId;
        if (!$redisClient->addExclusiveLock($exclusiveLockName, 30)) {
            //添加分布式锁
            throw new RuntimeException('添加排他锁[' . $exclusiveLockName . ']失败');
        }

        //统计职位下各流程标签中包含简历数目
        $caseResumeCountInFlowKey = 'caseresumecount:' . $caseId;
        $redisClient->del($caseResumeCountInFlowKey);
        foreach ($caseResumeCount as $key => $value) {
            $redisClient->hSet($caseResumeCountInFlowKey, $key, $value);
        }
        unset($caseResumeCount);
        //写入简历id明细
        foreach ($caseResumeList as $itemFlowat => $item) {
            $flowAtKeys = 'flowatstatuskeys:' . $caseId . ':' . $itemFlowat;
            $redisClient->del($flowAtKeys);
            foreach ($item as $itemFlowatStatus => $itemResumeIds) {
                $resumeListKey = 'caseresumelist:' . $caseId . ':' . $itemFlowat . ':' . $itemFlowatStatus;
                $redisClient->del($resumeListKey);
                while ($ids = array_splice($itemResumeIds, 0, 50)) {
                    $redisClient->sAddArray($resumeListKey, $ids);
                }
                $redisClient->sAdd($flowAtKeys, $itemFlowatStatus);
            }
        }
        unset($caseResumeList);

        //执行完成，移除排它锁
        $redisClient->removeExclusiveLock($exclusiveLockName);

        return 0;
    }

    /**
     * 获取职位下简历总数
     * @param array $caseIds
     * @return array
     */
    public function getCaseResumeCountReport(array $caseIds) {
        $redisClient = Redis::connect('caseflowcount');
        $result = [];
        foreach ($caseIds as $caseId) {
            $caseResumeCountInFlowKey = 'caseresumecount:' . $caseId;
            $result[$caseId] = $redisClient->hGetAll($caseResumeCountInFlowKey);
        }

        return $result;
    }

    /**
     * 添加kpi报表数据
     * @param $oprId
     * @param $teamCode
     * @param $subId
     * @param $projectId
     * @param $caseId
     * @param $flowAt
     * @param $resumeIds
     */
    protected function addKpiReportData($oprId, $teamCode, $subId, $projectId, $caseId, $flowAt, $resumeIds) {
        $redisClient = Redis::connect('reportdata');
        $exclusiveLockName = 'reportlock:' . $oprId;
        if (!$redisClient->addExclusiveLock($exclusiveLockName)) {
            //添加分布式锁
            throw new RuntimeException('添加排他锁[' . $exclusiveLockName . ']失败');
        }

        $userId = date('Ymd') . ':' . $oprId . '_' . ($teamCode ?: '00000');

        $reportDataKey = 'reportcase:' . $userId . '_' . $caseId . ':' . $flowAt;
        $redisClient->sAddArray($reportDataKey, $resumeIds);

        $reportDataKey = 'reportproject:' . $userId . '_' . $projectId . ':' . $flowAt;
        $redisClient->sAddArray($reportDataKey, $resumeIds);

        $reportDataKey = 'reportcustomer:' . $userId . '_' . $subId . ':' . $flowAt;
        $redisClient->sAddArray($reportDataKey, $resumeIds);

        $reportDataKey = 'report:' . $userId . '_' . 0 . ':' . $flowAt;
        $redisClient->sAddArray($reportDataKey, $resumeIds);

        //执行完成，移除排它锁
        $redisClient->removeExclusiveLock($exclusiveLockName);
    }

    /**
     * 获取职位信息
     * @param $caseId
     * @param null $field
     * @return mixed
     */
    public function getCaseInfo($caseId, $field = null) {
        $field = $field ?: $this->field;
        $sql = 'SELECT ' . $field . ' FROM ' . $this->tablename . ' WHERE caseid=? ';
        $var = [$caseId];
        return Db::selectOne($sql, $var);
    }

    /**
     * 根据id获取职位列表
     * @param $caseIds
     * @param null $field
     * @return array
     */
    public function getCaseList($caseIds, $field = null) {
        if (empty($caseIds)) {
            return [];
        }
        $field = $field ?: $this->field;
        $inFlag = substr(str_repeat('?,', count($caseIds)), 0, -1);
        $sql = 'SELECT ' . $field . ' FROM ' . $this->tablename . ' WHERE caseid IN(' . $inFlag . ') ';
        $var = $caseIds;
        return Db::select($sql, $var);
    }


    public function getProjectsWithOpr($subId, $oprId) {
        if (is_null($subId)) {
            return [];
        }
        $sql = 'SELECT Project.projectid,projectname FROM ProjectOperator
                    LEFT JOIN Project ON Project.projectid=ProjectOperator.projectid WHERE oprid=? AND status=?';
        $var = [$oprId, '01'];
        $projectList = Db::select($sql, $var);
        return $projectList;
    }

    protected function chkProjectStatus($id, $field = 'caseid') {
        if ($field == 'projectid') {
            $sql = 'select projectid from Project where projectid = ? and status = ?';
            $var = [$id, '01'];
            $result = Db::selectOne($sql, $var);
        } else if ($field == 'caseid') {
            $sql = 'select caseid,sc.subid,sc.projectid from SCase as sc left join Project as p on p.projectid=sc.projectid where sc.caseid = ? and p.status = ? ';
            $var = [$id, '01'];
            $result = Db::selectOne($sql, $var);
        }
        return $result;
    }

    public function getCaseCount() {
        $sql = 'select projectid,count(1) as count from SCase where status = ? group by projectid';
        $var = ['01'];
        $result = Db::select($sql, $var);
        $caseCount = [];
        foreach ($result as $item) {
            $caseCount[$item['projectid']] = $item['count'];
        }
        return $caseCount;
    }

    /**
     * 职位管理、人才管理列表：将交易公司在列表中置顶 或 取消置顶
     * @param $oprId 操作人id
     * @param $subId 交易公司id
     * @param $status 置顶-1 取消置顶-0
     * @return array
     */
    public function topSub($oprId, $subId, $status) {
        $redisClient = Redis::connect('topsubids');
        $keyName = 'topsubids:unfinished:oprid:' . $oprId;
        if ($status) {
            $count = $redisClient->zCard($keyName);
            if ($count > 100) {
                return [
                    'status' => false,
                    'message' => '置顶数已到上限'
                ];
            }
            $redisClient->zRem($keyName, $subId);//避免重复插值
            $result = $redisClient->zAdd($keyName, [], date("YmdHis"), $subId);
        } else {
            $result = $redisClient->zRem($keyName, $subId);
        }
        if (!$result) {
            return [
                'status' => false,
                'message' => '操作失败'
            ];
        }
        return [
            'status' => true,
            'message' => '操作成功'
        ];
    }

    /**
     * 设置职位沟通表头
     * @param $caseId
     * @param $contactFieldId
     * @return array
     */
    public function setCaseContactField($caseId, $fieldIdList) {
        if (empty($fieldIdList)) {
            return [
                'status' => false,
                'message' => '沟通表头不能为空'
            ];
        }

        $result = $this->setContactField($caseId, $fieldIdList);
        return $result;
    }

    /**
     * 职位未定义时设置职位沟通表头
     * @param $caseId
     * @param $contactFieldId
     * @return array
     */
    public function setCaseContactField2($caseId, $fieldIdList) {
        if (empty($fieldIdList)) {
            return [
                'status' => false,
                'message' => '沟通表头不能为空'
            ];
        }

        $sql = 'select caseid from SCase where caseid = ? and hascontact = ? and status = ?';
        $var = [$caseId, 1, '01'];
        $result = Db::selectOne($sql, $var);
        if (!empty($result)) {
            return [
                'status' => false,
                'message' => '该职位已定义沟通表头'
            ];
        }

        $result = $this->setContactField($caseId, $fieldIdList);
        return $result;
    }

    public function setContactField($caseId, $fieldIdList) {
        $oprRight = $this->chkCaseEditRight($caseId);
        if (!$oprRight['status']) {
            return $oprRight;
        }
        $projectId = $oprRight['projectid'];
        //校验选择的表头列表是否合法
        $projectModel = new ProjectModel();
        $allfieldIdList = array_column($projectModel->getContactFieldList($projectId)['contactfieldlist'], 'id');
        $selectIdList = explode(',', $fieldIdList);
        if (!empty(array_diff($selectIdList, $allfieldIdList)) || empty($allfieldIdList)) {
            return [
                'status' => false,
                'message' => '存在非法表头'
            ];
        }
        //检验完成，进行添加
        //检查职位是否已定义交付方式
        $sql = 'select id from CaseContactField where caseid = ? and status = ?';
        $var = [$caseId, '01'];
        $result = Db::selectOne($sql, $var);
        $oprInfo = UserAuth::getLoginInfo();
        //未定义则新增
        if (empty($result)) {
            $insertSql = 'insert into CaseContactField (caseid,fieldidlist,creater,creatername,updater,updatername,status,createdate,updatedate)
                    values (?,?,?,?,?,?,?,Now(),Now())';
            $insertVar = [
                $caseId,
                $fieldIdList,
                $oprInfo['userid'],
                $oprInfo['cname'],
                $oprInfo['userid'],
                $oprInfo['cname'],
                '01',
            ];
            $statement1 = Db::connect()->prepareStatement($insertSql, $insertVar);
            $updateSql = 'update SCase set hascontact = ? where caseid = ?';
            $updateVar = [1, $caseId];
            $statement2 = Db::connect()->prepareStatement($updateSql, $updateVar);
            $result = Db::connect()->prepareTransactionStatement(false, $statement1, $statement2)->execute();
        } else { //已定义则更新
            $updateSql = 'update CaseContactField set fieldidlist = ?,updater = ?,updatername =?,updatedate=Now() where caseid = ? and status = ?';
            $updateVar = [
                $fieldIdList,
                $oprInfo['userid'],
                $oprInfo['cname'],
                $caseId,
                '01'
            ];
            $result = Db::update($updateSql, $updateVar);
        }

        if (!$result) {
            Log::error('职位id:' . $caseId . '选择沟通表头失败');
            return [
                'status' => false,
                'message' => '添加失败'
            ];
        }
        return [
            'status' => true,
            'message' => '添加成功'
        ];
    }

    /**
     * 设置职位交付方式
     * @param $caseId
     * @param $chargeId
     * @param $chargeStandardId
     * @return array
     */
    public function setCaseChargeWay($caseId, $chargeStandardId) {
        if (empty($chargeStandardId)) {
            return [
                'status' => false,
                'message' => '交付方式不能为空'
            ];
        }
        $oprRight = $this->chkCaseEditRight($caseId);
        if (!$oprRight['status']) {
            return $oprRight;
        }

        //检验交付方式id是否存在
        $sql = 'select chargeid from ChargeStandard where id = ? and status = ?';
        $var = [$chargeStandardId, '01'];
        $result = Db::selectOne($sql, $var);
        if (empty($result)) {
            return [
                'status' => false,
                'message' => '存在非法交付方式'
            ];
        }
        $chargeId = $result['chargeid'];
        //检验完成，进行添加
        //检查职位是否已定义交付方式
        $sql = 'select id from CaseChargeWay where caseid = ? and status = ?';
        $var = [$caseId, '01'];
        $result = Db::selectOne($sql, $var);
        $oprInfo = UserAuth::getLoginInfo();
        //未定义则新增
        if (empty($result)) {
            $insertSql = 'insert into CaseChargeWay (caseid,chargewayid,chargestandardid,creater,creatername,updater,updatername,status,createdate,updatedate)
                    values (?,?,?,?,?,?,?,?,Now(),Now())';
            $insertVar = [
                $caseId,
                $chargeId,
                $chargeStandardId,
                $oprInfo['userid'],
                $oprInfo['cname'],
                $oprInfo['userid'],
                $oprInfo['cname'],
                '01',
            ];
            $statement1 = Db::connect()->prepareStatement($insertSql, $insertVar);
            $updateSql = 'update SCase set haschargeway = ? where caseid = ?';
            $updateVar = [1, $caseId];
            $statement2 = Db::connect()->prepareStatement($updateSql, $updateVar);
            $result = Db::connect()->prepareTransactionStatement(false, $statement1, $statement2)->execute();
        } else { //已定义则更新
            $updateSql = 'update CaseChargeWay set chargewayid = ?,chargestandardid = ?,updater = ?,updatername =?,updatedate=Now() where caseid = ? and status = ?';
            $updateVar = [
                $chargeId,
                $chargeStandardId,
                $oprInfo['userid'],
                $oprInfo['cname'],
                $caseId,
                '01'
            ];
            $result = Db::update($updateSql, $updateVar);
        }

        if (!$result) {
            Log::error('职位id:' . $caseId . '设置交付方式id:' . $chargeId . '失败');
            return [
                'status' => false,
                'message' => '添加失败'
            ];
        }
        return [
            'status' => true,
            'message' => '添加成功'
        ];
    }

    /**
     * 获取职位沟通表头信息列表
     * @param $caseId
     * @param $withDetailInfo   是否需要查询沟通表头的详细信息
     * @return array
     */
    public function getCaseContactFieldList($caseId, $withDetailInfo, $keyCode = null) {
        if (empty($caseId)) {
            return ['status' => false, 'message' => '职位id不能为空'];
        }
        $sql = 'select projectid from SCase where caseid = ?';
        $var = [$caseId];
        $projectInfo = Db::selectOne($sql, $var);
        $projectId = $projectInfo['projectid'] ?? 0;
        if (empty($projectId)) {
            return ['status' => false, 'message' => '职位不存在'];
        }
        $sql = 'select oprid from CaseOperator where caseid = ? and status = ?';
        $var = [$caseId, '01'];
        $opr1 = array_column(Db::select($sql, $var), 'oprid');

        $teamCode = UserAuth::getLoginInfo('teamcode');
        $teamCodeLength = strlen($teamCode);
        $teamList = [];
        $length = 5;
        while ($length <= $teamCodeLength) {
            $teamList[] = substr($teamCode, 0, $length);
            $length += 5;
        }
        $inFlag = substr(str_repeat('? ,', (count($teamList))), 0, -1);
        $sql = 'select userid from User where teamcode in (' . $inFlag . ') and teamrole = ? and status = ?';
        $var = array_merge($teamList, [2, '01']);
        $opr2 = array_column(Db::select($sql, $var), 'oprid');

        $caseOprIds = array_merge($opr1, $opr2);
        if (!in_array(UserAuth::getLoginId(), $caseOprIds)) {
            return [
                'status' => false,
                'message' => '非职位执行人，无操作权限'
            ];
        }
        //查询表头详细信息
        $sql = 'select fieldidlist from CaseContactField where caseid = ? and status = ?';
        $var = [$caseId, '01'];
        $caseContactFieldIdList = Db::selectOne($sql, $var);
        $caseContactFieldIdList = isset($caseContactFieldIdList['fieldidlist']) ? explode(',', $caseContactFieldIdList['fieldidlist']) : [];

        if (!$withDetailInfo) {
            return ['status' => true, 'message' => '获取成功', 'list' => $caseContactFieldIdList];
        }
        if (!empty($caseContactFieldIdList)) {
            $sql = 'SELECT id,fieldname,showname,unit,type,arguments FROM ContactField WHERE ID IN(' . mergeArrSql($caseContactFieldIdList) . ') AND projectid=? AND status=?';
            $var = array_merge($caseContactFieldIdList, [$projectId, '01']);
            $list = Db::select($sql, $var);
            $caseContactFieldList = [];
            foreach ($list as $item) {
                $caseContactFieldList[$item['id']] = $item;
            }
            unset($list);
            $result = [];
            foreach ($caseContactFieldIdList as $item) {
                if (isset($caseContactFieldList[$item])) {
                    $caseContactFieldList[$item]['arguments'] = json_decode($caseContactFieldList[$item]['arguments']);
                    $result[] = $caseContactFieldList[$item];
                }
            }
            $detailInfo = [];
            if (!empty($keyCode)) {
                //根据keycode获取填写的信息详情
                $detailInfoSql = 'SELECT rc.id,rc.caseid,rc.content as content,rr.content as remark  FROM ResumeContact as rc left join ResumeRemark as rr on rr.id=rc.remarkid
                            WHERE rc.caseid=? AND keycode=? AND rc.status=?';
                $detailInfoVar = [$caseId, $keyCode, '01'];
                $detailInfo = Db::connect()->selectOne($detailInfoSql, $detailInfoVar);
                if (!empty($detailInfo['content'])) {
                    $detailInfo['content'] = json_decode($detailInfo['content']);
                }
            }
            return ['status' => true, 'message' => '获取成功', 'list' => $result, 'detailinfo' => $detailInfo];
        } else {
            return ['status' => true, 'message' => '获取成功', 'list' => []];
        }
    }

    /**
     * Notes:获取职位沟通表头信息列表 -- 项目权限
     * @param $caseId
     * @param $withDetailInfo
     * @param null $keyCode
     * @return array
     */
    public function getCaseContactFieldListByProject($caseId, $withDetailInfo, $keyCode = null) {
        if (empty($caseId)) {
            return ['status' => false, 'message' => '职位id不能为空'];
        }
        $sql = 'select projectid from SCase where caseid = ?';
        $projectInfo = Db::selectOne($sql, [$caseId]);
        $projectId = $projectInfo['projectid'] ?? 0;
        if (empty($projectId)) {
            return ['status' => false, 'message' => '职位不存在'];
        }
        $right = (new Project())->chkGetRight($projectId);
        if (!$right) {
            return [
                'status' => false,
                'message' => '无操作权限'
            ];
        }
        //查询表头详细信息
        $sql = 'select fieldidlist from CaseContactField where caseid = ? and status = ?';
        $var = [$caseId, '01'];
        $caseContactFieldIdList = Db::selectOne($sql, $var);
        $caseContactFieldIdList = isset($caseContactFieldIdList['fieldidlist']) ? explode(',', $caseContactFieldIdList['fieldidlist']) : [];

        if (!$withDetailInfo) {
            return ['status' => true, 'message' => '获取成功', 'list' => $caseContactFieldIdList];
        }
        if (!empty($caseContactFieldIdList)) {
            $sql = 'SELECT id,fieldname,showname,unit,type,arguments FROM ContactField WHERE ID IN(' . mergeArrSql($caseContactFieldIdList) . ') AND projectid=? AND status=?';
            $var = array_merge($caseContactFieldIdList, [$projectId, '01']);
            $list = Db::select($sql, $var);
            $caseContactFieldList = [];
            foreach ($list as $item) {
                $caseContactFieldList[$item['id']] = $item;
            }
            unset($list);
            $result = [];
            foreach ($caseContactFieldIdList as $item) {
                if (isset($caseContactFieldList[$item])) {
                    $caseContactFieldList[$item]['arguments'] = json_decode($caseContactFieldList[$item]['arguments']);
                    $result[] = $caseContactFieldList[$item];
                }
            }
            $detailInfo = [];
            if (!empty($keyCode)) {
                //根据keycode获取填写的信息详情
                $detailInfoSql = 'SELECT rc.id,rc.caseid,rc.content as content,rr.content as remark  FROM ResumeContact as rc left join ResumeRemark as rr on rr.id=rc.remarkid
                            WHERE rc.caseid=? AND keycode=? AND rc.status=?';
                $detailInfoVar = [$caseId, $keyCode, '01'];
                $detailInfo = Db::connect()->selectOne($detailInfoSql, $detailInfoVar);
                if (!empty($detailInfo['content'])) {
                    $detailInfo['content'] = json_decode($detailInfo['content']);
                }
            }
            return ['status' => true, 'message' => '获取成功', 'list' => $result, 'detailinfo' => $detailInfo];
        } else {
            return ['status' => true, 'message' => '获取成功', 'list' => []];
        }
    }

    /**
     * 查询职位操作权限
     * @param $caseId
     * @return array
     */
    protected function checkCaseOprRight($caseId) {
        if (empty($caseId)) {
            return ['status' => false, 'message' => '职位id不能为空'];
        }
        $sql = 'select projectid from SCase where caseid = ?';
        $var = [$caseId];
        $projectInfo = Db::selectOne($sql, $var);
        $projectId = $projectInfo['projectid'] ?? 0;
        if (empty($projectId)) {
            return ['status' => false, 'message' => '职位不存在'];
        }
        //操作人权限校验
        $projectModel = new ProjectModel();
        $result = $projectModel->chkEditRight($projectId);
        if (!$result) {
            return ['status' => false, 'message' => '您不是项目负责人，无权进行该操作'];
        }
        return ['status' => true, 'message' => '', 'projectid' => $projectId];
    }

    /**
     * 获取职位交付方式信息
     * @param $caseId
     * @return array
     */
    public function getCaseChargeWay($caseId) {
        if (empty($caseId)) {
            return ['status' => false, 'message' => '职位id不能为空'];
        }
        $sql = 'select caseid from SCase where caseid = ?';
        $var = [$caseId];
        $result = Db::selectOne($sql, $var);
        if (empty($result)) {
            return ['status' => false, 'message' => '职位不存在'];
        }
        $sql = 'select oprid from CaseOperator where caseid = ? and status = ?';
        $var = [$caseId, '01'];
        $opr1 = array_column(Db::select($sql, $var), 'oprid');

        $teamCode = UserAuth::getLoginInfo('teamcode');
        $teamCodeLength = strlen($teamCode);
        $teamList = [];
        $length = 5;
        while ($length <= $teamCodeLength) {
            $teamList[] = substr($teamCode, 0, $length);
            $length += 5;
        }
        $inFlag = substr(str_repeat('? ,', (count($teamList))), 0, -1);
        $sql = 'select userid from User where teamcode in (' . $inFlag . ') and teamrole = ? and status = ?';
        $var = array_merge($teamList, [2, '01']);
        $opr2 = array_column(Db::select($sql, $var), 'oprid');

        $caseOprIds = array_merge($opr1, $opr2);
        if (!in_array(UserAuth::getLoginId(), $caseOprIds)) {
            return [
                'status' => false,
                'message' => '非职位执行人，无操作权限'
            ];
        }
        $sql = 'select chargestandardid from CaseChargeWay where caseid = ? and status = ?';
        $var = [$caseId, '01'];
        $caseChargeWayInfo = Db::selectOne($sql, $var);

        return [
            'status' => true,
            'message' => '获取成功',
            'info' => $caseChargeWayInfo
        ];
    }

    public function chkCaseEditRight($caseId) {
        if (empty($caseId)) {
            return ['status' => false, 'message' => '职位id不能为空'];
        }
        $sql = 'select projectid from SCase where caseid = ?';
        $var = [$caseId];
        $projectInfo = Db::selectOne($sql, $var);
        $projectId = $projectInfo['projectid'] ?? 0;
        if (empty($projectId)) {
            return ['status' => false, 'message' => '职位不存在'];
        }
        $sql = 'select oprid from CaseOperator where caseid=? and status=?';
        $var = [$caseId, '01'];
        $result = Db::select($sql, $var);
        $caseOprs = array_column($result, 'oprid');
        $oprId = UserAuth::getLoginId();
        if (!in_array($oprId, $caseOprs)) {
            return [
                'status' => false,
                'message' => '非职位执行人，无操作权限'
            ];
        }
        return [
            'status' => true,
            'message' => '',
            'projectid' => $projectId
        ];
    }

    /**
     * 获取客户下所有职位信息
     * @param $subId
     * @param $rowFrom
     * @param $rowTo
     * @param $pageNum
     * @param $count
     * @param $caseName
     * @param $projectName
     * @return array
     */
    public function getSubCaseList($subId, $rowFrom, $rowTo, $pageNum, $caseName = '', $projectName = '', $projectId = 0) {
        $sql = 'SELECT caseid,casename,c.projectid,c.projectname FROM SCase a left join Project c on a.projectid = c.projectid ';
        $where = ' WHERE a.subid=? AND a.status!=? ';
        $var = [$subId, '03'];
        if (!empty($caseName)) {
            $where .= ' and a.casename like ? ';
            $var[] = '%' . $caseName . '%';
        }
        if (!empty($projectName)) {
            $where .= ' and c.projectname like ? ';
            $var[] = '%' . $projectName . '%';
        }
        if (!empty($projectId)) {
            $where .= ' and c.projectid = ? ';
            $var[] = $projectId;
        }
        $sql .= $where;
        $caseList = Db::select($sql, $var, $rowFrom, $rowTo);
        $count = count(Db::select($sql, $var));
        return [
            'status' => 'true',
            'message' => '获取成功',
            'pagenum' => $pageNum,
            'totalcount' => $count,
            'subcaselist' => $caseList
        ];
    }

    /**
     * 根据用户id获取有权限编辑的职位列表
     * @param $subId
     * @param $oprId
     * @param $rowFrom
     * @param $rowTo
     * @param $pageNum
     * @param string $caseName
     * @param string $projectName
     * @return array
     */
    public function getActiveSubCaseListByOprId($subId, $oprId, $rowFrom, $rowTo, $pageNum, $caseName = '', $projectName = '', $projectId = 0) {
        $sql = 'SELECT projectid FROM ProjectOperator WHERE status=? AND oprid=? AND subid=? AND ischarger=?';
        //$sql = 'SELECT ischarger FROM ProjectOperator WHERE status=? AND oprid=? AND projectid=?';
        $var = ['01', $oprId, $subId, 1];
        $result = Db::select($sql, $var);
        if (empty($result)) {
            return [
                'status' => 'true',
                'message' => '获取成功',
                'pagenum' => 1,
                'totalcount' => 0,
                'subcaselist' => []
            ];
        }
        $projectIdArr = [];
        foreach ($result as $item) {
            $projectIdArr[] = $item['projectid'];
        }
        $inFlag = substr(str_repeat('? ,', (count($projectIdArr))), 0, -1);
        $sql = 'select caseid,casename,c.projectid,c.projectname from SCase a left join Project c on a.projectid = c.projectid ';

        $where = ' where a.projectid in  ( ' . $inFlag . ' ) and a.status != ?';
        $var = array_merge([$projectIdArr], ['03']);
        if (!empty($caseName)) {
            $where .= ' and a.casename like ? ';
            $var[] = '%' . $caseName . '%';
        }
        if (!empty($projectName)) {
            $where .= ' and c.projectname like ? ';
            $var[] = '%' . $projectName . '%';
        }
        if (!empty($projectId)) {
            $where .= ' and c.projectid = ? ';
            $var[] = $projectId;
        }
        $sql .= $where;

        $caseList = Db::select($sql, $var, $rowFrom, $rowTo);
        $count = count(Db::select($sql, $var));
        return [
            'status' => 'true',
            'message' => '获取成功',
            'pagenum' => $pageNum,
            'totalcount' => $count,
            'subcaselist' => $caseList
        ];
    }

    /**
     * 根据teamcode获取有编辑权限的职位列表
     * @param $subId
     * @param $teamCode
     * @param $rowFrom
     * @param $rowTo
     * @param $pageNum
     * @param string $caseName
     * @param string $projectName
     * @return array
     */
    public function getActiveSubCaseListByTeamCode($subId, $teamCode, $rowFrom, $rowTo, $pageNum, $caseName = '', $projectName = '', $projectId = 0) {
        $sql = 'select po.projectid from ProjectOperator po LEFT JOIN User u on po.oprid=u.userid WHERE po.status=? AND u.status=? AND u.teamcode LIKE ? AND ischarger = ? AND subid = ?';
        //$sql = 'select max(ischarger) as ischarger from ProjectOperator po LEFT JOIN User u on po.oprid=u.userid WHERE po.status=? AND u.status=? AND u.teamcode LIKE ? AND po.projectid=?';
        $var = ['01', '01', $teamCode . '%', 1, $subId];
        $result = Db::select($sql, $var);
        if (empty($result)) {
            return [
                'status' => 'true',
                'message' => '获取成功',
                'pagenum' => 1,
                'totalcount' => 0,
                'subcaselist' => []
            ];
        }
        $projectIdArr = [];
        foreach ($result as $item) {
            $projectIdArr[] = $item['projectid'];
        }
        $inFlag = substr(str_repeat('? ,', (count($projectIdArr))), 0, -1);
        $sql = 'select caseid,casename,c.projectid,c.projectname from SCase a left join Project c on a.projectid = c.projectid ';

        $where = ' where a.projectid in  ( ' . $inFlag . ' ) and a.status != ?';
        $var = array_merge($projectIdArr, ['03']);

        if (!empty($caseName)) {
            $where .= ' and a.casename like ? ';
            $var[] = '%' . $caseName . '%';
        }
        if (!empty($projectName)) {
            $where .= ' and c.projectname like ? ';
            $var[] = '%' . $projectName . '%';
        }
        if (!empty($projectId)) {
            $where .= ' and c.projectid = ? ';
            $var[] = $projectId;
        }
        $sql .= $where;
        $caseList = Db::select($sql, $var, $rowFrom, $rowTo);
        $count = count(Db::select($sql, $var));
        return [
            'status' => 'true',
            'message' => '获取成功',
            'pagenum' => $pageNum,
            'totalcount' => $count,
            'subcaselist' => $caseList
        ];
    }

    /**
     * 将联系人批量添加到多个职位中
     * @param $caseIds
     * @param $contactorId
     * @return array
     */
    public function batchAddContactor($caseIds, $contactorId) {
        if (empty($caseIds)) {
            return [
                'status' => false,
                'message' => '所选职位不能为空'
            ];
        }
        if (empty($contactorId)) {
            return [
                'status' => false,
                'message' => '所选联系人不能为空'
            ];
        }
        $caseIdArr = explode(',', $caseIds);
        $inFlag = substr(str_repeat('? ,', (count($caseIdArr))), 0, -1);
        $sql = 'select projectid,subid,caseid from SCase where caseid in (' . $inFlag . ') and status != ?';
        $var = array_merge($caseIdArr, ['03']);
        $result = Db::select($sql, $var);
        if (empty($result)) {
            return [
                'status' => false,
                'message' => '所选职位不存在或已结束'
            ];
        }
        if (count($result) != count($caseIdArr)) {
            $tmpArr = [];
            foreach ($result as $item) {
                $tmpArr[] = $item['caseid'];
            }
            return [
                'status' => false,
                'message' => '存在不可选职位',
                'errorcaseids' => array_values(array_diff($caseIdArr, $tmpArr))
            ];
        }

        $subId = $result[0]['subid'];

        $sql = 'select id from CustomerAccount where id = ? and subid = ? and status = ?';
        $var = [$contactorId, $subId, '01'];
        $contactorInfo = Db::selectOne($sql, $var);
        if (empty($contactorInfo)) {
            return [
                'status' => false,
                'message' => '所选联系人不存在'
            ];
        }

        $projectModel = new ProjectModel();
        //获取交易公司下所有有编辑权限的项目id
        $hasEditRightProjectIdArr = $projectModel->getHasEditRightProjects($subId);
        $hasEditRightProjectIds = [];
        if (!empty($hasEditRightProjectIdArr)) {
            foreach ($hasEditRightProjectIdArr as $item)
                $hasEditRightProjectIds[] = $item['projectid'];
        }
        foreach ($result as $project) {
            if (!in_array($project['projectid'], $hasEditRightProjectIds)) {
                return [
                    'status' => false,
                    'message' => '存在无权编辑的项目'
                ];
            }
        }

        //获取职位下已存在的联系人
        $sql = 'select accountid,caseid from CaseContacter where caseid in (' . $inFlag . ') and status = ?';
        $var = array_merge($caseIdArr, ['01']);
        $result = Db::select($sql, $var);
        $tmpList = [];
        foreach ($result as $item) {
            $tmpList[$item['caseid']][] = $item['accountid'];
        }

        $oprId = UserAuth::getLoginId();
        $insertSql = 'insert into CaseContacter (subid,caseid,accountid,status,creator,createdate,updator,updatedate) values';
        $insertVar = $insertVarsSql = [];
        foreach ($caseIdArr as $caseId) {
            if (!empty($tmpList) && isset($tmpList[$caseId]) && in_array($contactorId, $tmpList[$caseId])) {
                continue;
            }
            $insertVarsSql[] = '(?,?,?,?,?,Now(),?,Now())';
            $insertVar = array_merge($insertVar, [
                $subId, $caseId, $contactorId, '01', $oprId, $oprId
            ]);
        }
        if (empty($insertVarsSql)) {
            return [
                'status' => false,
                'message' => '无新增联系人'
            ];
        }
        $insertSql .= implode(',', $insertVarsSql);
        $result = Db::connect('jyrpo_pdo')->insert($insertSql, $insertVar);
        if ($result) {
            return [
                'status' => true,
                'message' => '联系人批量加入职位成功'
            ];
        }
        return [
            'status' => false,
            'message' => '联系人批量加入职位失败'
        ];
    }

    /**
     * 获取职位的交付方式
     * @param $caseIds
     * @return array
     */
    public function getCaseListChargeWay($caseIds) {
        if (empty($caseIds)) {
            return [];
        }
        $inFlag = substr(str_repeat('? ,', (count($caseIds))), 0, -1);
        $sql = 'select a.id,a.projectid,a.subid,a.chargeway,a.targetnum,a.bindflow,b.caseid from ChargeWay a LEFT JOIN CaseChargeWay b ON a.id=b.chargewayid WHERE b.caseid IN (' . $inFlag . ') AND a.status=? AND b.`status`=?';
        $var = array_merge($caseIds, ['01', '01']);
        return Db::select($sql, $var);
    }
}